Group data rows by 'count distinct' value

[aoifoc]

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

[MarcelBeug]

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 · Accepted solution]

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]

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]

@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.