Forum Discussion

aoifoc's avatar
aoifoc
Frequent Visitor
9 years ago
Solved

Group data rows by 'count distinct' value

Hi,   I have dataset which includes multiple rows of activity data for individuals, this is a small example:   Name         Type Mark            Gym Mark            Outdoor Mark            Fit...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    In DAX you could create the following Table

     

    New Table = SUMMARIZECOLUMNS('Table1'[Name],"Types",DISTINCTCOUNT('Table1'[Type]))

    Then create a measure on that new table 

     

    Measure = COUNTROWS('New Table')

    Then if you add the items to a grid you can display your end result

     

     

     

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
9 years ago

aoifoc

If you want to do this at the report level it is as simple as creating a measure for the Distinct Count of activities.

Total Activities = DISTINCTCOUNT('TableName'[Type])

Then build your table with your Names on the row and the value of Total Activities. You may need to add a lookup table of names but then again, you may not. Depends on your data.

You data question actually specifies the opposite of this but I had already typed it so I will continue that train of thought by saying the same can be accomplished by using distinct count of names instead of activities.