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

     

     

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
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

 

 

 

aoifoc's avatar
aoifoc
Frequent Visitor
9 years ago

Thank you for the help on this!

 

I ended up creating the calculated table suggested by Phil_Seamark (a solution also partially suggested by MarcelBeug) and this did the job - I have the results I need now in a matrix in my report.

 

Thank you all.