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            Fitness Centre
Mark            Club
Tom             Fitness Centre
Tom             Club
Sarah           Gym
Sarah           Outdoor
Sarah           Club
Paul             Gym
Paul             Outdoor
Paul             Outdoor

Jess              Club

 

I want to create a visual which counts "how many individuals did all 4 types of activity, how many did 3, how many did 2, how many did 1" based on the combinations of ID + Type.

 

Using the above example:

 

Mark = four types

Sarah = three types

Tom = two types

Paul = two types

Jess = one type

 

So the actual result that I'm looking for is:

 

No. Activities        Count

Four                        1

Three                      1

Two                        2

One                        1

 

My dataset has several thousand rows, and there are other columns present (with non-distinct values) so I can't aggregate the dataset itself. I'll need to do the aggregation within the report somehow.

 

I hope I've explained this well, any help will be appreciated.

 

Aoife

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

     

     

     

4 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    Somehow you need to do something in your dataset.

     

    My suggestion would be to add a table with the aggregations, but it seems that's not a prefered solution so I created a solution in M (Edit Queries) as you can see in this video, illustrating the results from each step of the following code (the video starts at "PreviousStep" which was the result of loading the table from an Excel workbook):

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Group data rows by count distinct value.xlsx"), null, true),
    Table3_Table = Source{[Item="Table3",Kind="Table"]}[Data],
    PreviousStep = Table.TransformColumnTypes(Table3_Table,{{"Name", type text}, {"Type", type text}}),
    AddedNoOfActivities = Table.AddColumn(PreviousStep, "No. Of Activities", (CurrentRecord) => List.Count(List.Distinct(Table.SelectRows(PreviousStep, each _[Name] = CurrentRecord[Name])[Type]))),
    AddedNoOfNames = Table.AddColumn(AddedNoOfActivities, "No. Of Names", (CurrentRecord) => List.Count(List.Select(AddedNoOfActivities[Name], each _ = CurrentRecord[Name]))),
    AddedCount = Table.AddColumn(AddedNoOfNames, "Count", each 1 / [No. Of Names]),
    RemovedNoOfNames = Table.RemoveColumns(AddedCount,{"No. Of Names"}),
    AddedNoActivities = Table.AddColumn(RemovedNoOfNames, "No. Activities", each {"One","Two","Three","Four"}{[No. Of Activities]-1}),
    Typed = Table.TransformColumnTypes(AddedNoActivities,{{"No. Of Activities", Int64.Type}, {"Count", type number}, {"No. Activities", type text}})
in
    Typed

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft 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.

  • kcantor's avatar
    kcantor
    Community 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.