topic Re: MAX count of Indexed rows by group in DAX Commands and Tips

MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 18:17:42 GMT

My goal is a list with a set of rows for each role_id having the maximum of any scrum team. The final list would have each row appended with the index value.

This is the source table

team_id role_id

Scrum_Team_1	Developer
Scrum_Team_1	Developer
Scrum_Team_1	Tester
Scrum_Team_2	Developer
Scrum_Team_2	Program_Manager
Scrum_Team_3	Developer
Scrum_Team_3	Developer
Scrum_Team_3	Developer
Scrum_Team_3	Tester
Scrum_Team_3	Program_Manager
Scrum_Team_3	Program_Manager

The final table would be

role_count

Tester1

Developer1

Developer2

Developer3

Program_Manager1

Program_Manager2

Any Ideas? I've being working on this a while. Thanks!

Re: MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 21:20:04 GMT

Unclear. The final table does not look correct. Please give us a better description of what it is you need. And - most important of all - do not make mistakes.

Thanks.

Best
Darek

Re: MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 21:30:47 GMT

There are 2 teams with 1 Tester

--> Gives 1 row

Tester1

There are 3 teams with developers, there is one team with 3 developers which is the most developers for all of the teams --> Gives 3 rows

Developer1

Developer2

Developer3

There are 2 teams with program managers, there is on team with 2 program managers which is the most program managers for all of the teams --> Gives 2 rows

Program_Manager1

Program_Manager2

Thanks for your time.

Re: MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 21:59:35 GMT

OK, now it's much clearer. Thanks.

Best
Darek

Re: MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 22:14:40 GMT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4uKs2ND0lNzI03VNJRckktS83JL0gtUorVIU0yJLW4BFPGCJ82kGRAUX56EZDtm5iXmI6pxBiffsoksTvYmDg3YSiJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [team_id = _t, role_id = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "team_id", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"team_id - Copy.1", "team_id - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"team_id - Copy.1", type text}, {"team_id - Copy.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"team_id - Copy.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"team_id - Copy.2", "team_id"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"role_id", "team_id"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"team_id"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns2", {"role_id"}, {{"MaxCount", each List.Max([Count]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "ListOfOneToMaxCount", each {1..[MaxCount]}),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom",{"MaxCount"}),
    #"Expanded ListOfOneToMaxCount" = Table.ExpandListColumn(#"Removed Columns3", "ListOfOneToMaxCount"),
    #"Added Custom1" = Table.AddColumn(#"Expanded ListOfOneToMaxCount", "RoleWithNumber", each [role_id] & Number.ToText([ListOfOneToMaxCount])),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom1",{"role_id", "ListOfOneToMaxCount"})
in
    #"Removed Columns4"

Re: MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 22:16:51 GMT

I was able to get a count by scrum team & role. I added a concatenanted column with team_id & role_id --> team_role_id, then added an additional column with

team_role_count = SUMX(Test_Table, IF( EARLIER(Test_Table[team_role_id]) = Test_Table[team_role_id],1,0))

I now get

team_id role_id team_role_id Count

Scrum_Team_1	Developer	Scrum_Team_1Developer	2
Scrum_Team_1	Developer	Scrum_Team_1Developer	2
Scrum_Team_1	Tester	Scrum_Team_1Tester	1 (KEEP)
Scrum_Team_2	Developer	Scrum_Team_2Developer	1
Scrum_Team_2	Program_Manager	Scrum_Team_2Program_Manager	1
Scrum_Team_3	Developer	Scrum_Team_3Developer	3 (KEEP)
Scrum_Team_3	Developer	Scrum_Team_3Developer	3 (KEEP)
Scrum_Team_3	Developer	Scrum_Team_3Developer	3 (KEEP)
Scrum_Team_3	Tester	Scrum_Team_3Tester	1
Scrum_Team_3	Program_Manager	Scrum_Team_3Program_Manager	2 (KEEP)
Scrum_Team_3	Program_Manager	Scrum_Team_3Program_Manager	2 (KEEP)

So far, now how do I just select the keepers?

Re: MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 22:19:00 GMT

You should avoid calculated columns and use them only if you don't have any other option. But you do have an option - I just showed you how to calculated this in Power Query. Calculated columns are not compressed efficiently but when you created them in Power Query, THEY ARE.

Best
Darek

Re: MAX count of Indexed rows by group

Anonymous — Mon, 12 Aug 2019 22:20:51 GMT

Wow. You are good. Thanks!!

Re: MAX count of Indexed rows by group

Anonymous — Tue, 13 Aug 2019 14:28:25 GMT

A follow-up question. When the custom column was added the formula was {1..[MaxCount]}. Please point me to the source of this function. Is it a list function or a basic formula? I'm looking for some documentation on the syntax so I can learn. Thanks!

Re: MAX count of Indexed rows by group

Anonymous — Tue, 13 Aug 2019 15:14:10 GMT

This is my manual intervention. It's not possible through the UI. It just so happens that I have read the whole documentation on M, so I know it throughout.

When I work with something, I always get to know my tools, so that I know exactly what I'm doing and why :)

Best
Darek