Forum Discussion

shankyy7227's avatar
shankyy7227
Frequent Visitor
8 years ago
Solved

distinct count of multiple columns

My data contains FName, LName, MName, Gender, Card ID, Health ID, Active Flag and there may be Null in any column for each row i am trying to calculate distinct count  (FName+Card ID+Health ID) and d...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Create two measures and try the following Dax

     

    Measure1 = COUNTROWS(GROUPBY(User,User[FNAME],User[Card ID],User[Health ID],User[Gender]))
    Measure2 = COUNTROWS(FILTER(GROUPBY(User,User[FNAME],User[Card ID],User[Health ID],User[Gender]),User[Gender]="M"))

     

     

     

marcorusso's avatar
marcorusso
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

Yes, calculated column is better for performance.

The measure will not perform well in that version, it's better to use SUMMARIZE instead of GROUPBY and CALCULATETABLE instead of FILTER, this way you don't have to materialize Gender:

Measure2 = 
CALCULATETABEL (
    COUNTROWS (
        SUMMARIZE ( User, User[FNAME], User[Card ID], User[Health ID] )
    ),
    User[Gender]="M"
)

 

jlopezfelizzola's avatar
jlopezfelizzola
New Member
2 years ago

Maestro!

Just a quick precision:

Measure2 = 
CALCULATE (
    COUNTROWS (
        SUMMARIZE ( User, User[FNAME], User[Card ID], User[Health ID] )
    ),
    User[Gender]="M"
)

 Change the CALCULATETABLE to simple  CALCULATE.

 

Have a great day!