COUNT DISTINCT

[racer25]

Hi 

I am new to PBI as our company has long since used Qlikview but transitioning to PBI.

I have spent a decent amount of time trying to figure this one out and while I am narrowing in on the answer I am either getting the wrong answer or errors.

I have 4 relebant fields

Worker , Approval Date, Expense ID, Amount

I am using a table and want to show Worker, Sum Amount by Approval Date Year and Distinct Count of Expense ID.

Sum Amount by Year is done.

Count Distinct Expense ID's is my problem= If the Worker has no claims in the Year the Count is 1 not zero.

Any thought that could help? I have tried various add column options and DISTINCYCOUNTNOBLANKS.

Thanks in advance,

Rob

[bhanu_gautam · Accepted solution]

@racer25 You need to create a measure that calculates the distinct count of Expense IDs for each worker and year, and handles cases where there are no claims correctly.

dax
DistinctExpenseCount =
VAR DistinctCount = DISTINCTCOUNT('YourTable'[Expense ID])
RETURN
IF(DistinctCount = BLANK(), 0, DistinctCount)

[racer25]

Thanks so much - PBI didn't like the VAR Name of DistinctCount but a quick tweak and working. It also gave me an understanding of how to do it not just copy/[aste - appreciate the support