DISTINCT Count from Column

DAX DAX Calculations
1

Hi,

I have a table from which I need to count distinct quantity ordered.
It is a bit difficult to explain. Hope the following example can help.

The Table

Item Qty
Apple 1
Banana 2
Apple 3
Pear 1
Orange 1

What I need

Qty Count
1 3
2 1
3 1

Appreciate your help.

2

@Robin,

Welcome to the forum - glad to have you here!

Give this a go:

  1. created a one column physical table, with the following calculated column:

Sequence = GENERATESERIES( 1, 100, 1)

This will provide the first column of your desired table

  1. created a measure to count the number of rows in the data table where the quantity equals the value of our GENERATESERIES column from above:

     Count Quantity = 
     CALCULATE(
     COUNTROWS( Data ),
     FILTER( 
         Data,
         Data[Quantity] = SELECTEDVALUE( 'Sequence'[Quantity] )
     )
 )

image

Full solution file posted below. Hope this is helpful.

1 Like
3

Hi Brian,

That worked perfectly :smile:

I tried with the Sequence table first. Since I am pulling the data from a SQL Server, I created a new query to pull distinct Quantity and replaced the Sequence table.

Thanks for your help. Much appreciated.

Robin

1 Like
4

Hi @Robin, Welcome to the Forum! It’s great to know that you’re issue has been resolved. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!