Distinct Count

[DenbyG]

Hi, I would like to do a distinct count of all the items in a coloumn 'LAR' that is ZPROG001, and all the items in another column, 'AES' that is 10 or 11, in Power Bi. I've tried several different DAX's but I can't seem to get it to work. I'm learning DAX so would appreciate some support.

[Shai_Karmani]

You can do this with a simple CALCULATE+ DISTINCTCOUNT.

If you want the distinct count of rows where LAR = "ZPROG001" and AES = 10 or 11, try:

meas_Count_LAR_AES =
CALCULATE(
DISTINCTCOUNT(YourTable[YourRowID]),
YourTable[LAR] = "ZPROG001",
YourTable[AES] IN { 10, 11 }
)

Replace YourTable with your table name and YourRowID with a unique row column.

If LAR is already unique per row, you can also distinct-count LAR directly:

meas_Count_LAR_AES =
CALCULATE(
DISTINCTCOUNT(YourTable[LAR]),
YourTable[LAR] = "ZPROG001",
YourTable[AES] IN { 10, 11 }
)

Shai Karmani | Data & Analytics

If it helped  please mark as resolved & give a kudo so others can find it too.

Let’s connect on LinkedIn

[DenbyG]

Hi, Sorry but it just returns a value of 1. I know there are more than that

Learning aim reference ZPROG001 Z0059747 ZPROG001 Z0059747 ZPROG001 ZPROG001 ZPROG001 ZPROG001 ZPROG001 ZPROG001 Z0059747 ZPROG001 ZPROG001 Z0059747 ZPROG001	Applicable employment status 11 0 11 0 2 10 10 10 11 10 0 11 10 0 1

[MasonMA]

Would you want to count unique items or all items? If it is a distinct count, the Measure should be all good. If you would want to count all rows that meet the conditions, then change the DISTINCTCOUNT() to COUNTROWS('table'). 

[DenbyG · Accepted solution]

Brilliant. Thank you. I appreciate that. It is CountRows.