topic Re: COUNT DISTINCT in DAX Commands and Tips

COUNT DISTINCT

racer25 — Wed, 30 Apr 2025 12:00:18 GMT

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

Re: COUNT DISTINCT

bhanu_gautam — Wed, 30 Apr 2025 12:22:34 GMT

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)

Re: COUNT DISTINCT

racer25 — Wed, 30 Apr 2025 12:48:37 GMT

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