Forum Discussion

racer25's avatar
racer25
Regular Visitor
1 year ago
Solved

COUNT DISTINCT

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...
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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)

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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's avatar
    racer25
    Regular Visitor
    1 year ago

    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