Forum Discussion
Helper IISummarize by two different field values grouping by another field
I have a table containing a Date, Client ID, and an Assistance Type. What I want to show in a grid is the year and then a count of Client IDs where a particular client ID has two specific Assistance Types (out of many) during that year. Example from below table is that I want a count of distinct clientIDs which have received both Type1 and Type2 assistance during that year. I've tried to create a table using Summarize but not having much luck. Any help is appreciated.
Table
| ClientID | Date of Assistance | Type of Assistance |
| 1 | 1/1/2022 | Type1 |
| 1 | 2/1/2022 | Type2 |
| 1 | 3/1/2022 | Type1 |
| 1 | 4/1/2022 | Type3 |
| 2 | 1/1/2022 | Type1 |
| 2 | 2/1/2022 | Type3 |
| 3 | 4/1/2022 | Type4 |
| 3 | 5/1/2022 | Type5 |
| 4 | 1/1/2022 | Type1 |
| 4 | 2/1/2022 | Type 2 |
| 4 | 3/1/2022 | Type 2 |
Desired Result: Something like below but don't necessarily need "Type 1 and Type 2" in a grid cell as the value of 2 may just be in a card particular to 2022.
| 2022 | Client ID Count |
| Type 1 and Type 2 | 2 |
hey jwessel , this is solvable is power query by duplicating of the aggregated table containing (ClientID, Type of Assistance and Year) and then merging them.
1. Aggregatelet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyAjJDKgtSDZVidSASRqgSRnAJY1w6TFAljMESRrjsMMK0A6LDGNMoE7iEKaqEKVjCBJcdJrj8YYLpD6BELAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, #"Date of Assistance" = _t, #"Type of Assistance" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Date of Assistance", type date}, {"Type of Assistance", type text}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date of Assistance]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"Date of Assistance"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns") in #"Removed Duplicates"2. Duplicate to have identical Table (2)
3. Merge, filter and group
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyAjJDKgtSDZVidSASRqgSRnAJY1w6TFAljMESRrjsMMK0A6LDGNMoE7iEKaqEKVjCBJcdJrj8YYLpD6BELAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, #"Date of Assistance" = _t, #"Type of Assistance" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Date of Assistance", type date}, {"Type of Assistance", type text}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date of Assistance]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"Date of Assistance"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns"), #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"ClientID", "Year"}, #"Table (2)", {"ClientID", "Year"}, "Table (2)", JoinKind.LeftOuter), #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Type of Assistance"}, {"Type of Assistance.1"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Table (2)", each [Type of Assistance] < [Type of Assistance.1] ), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Type of Assistance", "Year", "Type of Assistance.1"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Merged Columns" = Table.CombineColumns(#"Grouped Rows",{"Type of Assistance", "Type of Assistance.1"},Combiner.CombineTextByDelimiter(" and ", QuoteStyle.None),"Merged") in #"Merged Columns"I hope this helps!
7 Replies
- Shaurya
Memorable Member
Hi jwessel,
You can use:
Result = SUMMARIZE(FILTER('Table', 'Table'[Type of Assistance]="Type1" || 'Table'[Type of Assistance]="Type2"), 'Table'[Year], "Count", DISTINCTCOUNT('Table'[ClientID]))Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts
jwesselHi Shaurya , I think this was double counting some of the client IDs. What I'm looking for is a count of where a particular client ID has BOTH the Type1 and Type2 assistance types within the same year and not either/or. When I created actual table with what I supplied initially and then incorporated the Summarize provided. My result was 3 when I was expecting 2.
alena2kResolver IV
hey jwessel , this is solvable is power query by duplicating of the aggregated table containing (ClientID, Type of Assistance and Year) and then merging them.
1. Aggregatelet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyAjJDKgtSDZVidSASRqgSRnAJY1w6TFAljMESRrjsMMK0A6LDGNMoE7iEKaqEKVjCBJcdJrj8YYLpD6BELAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, #"Date of Assistance" = _t, #"Type of Assistance" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Date of Assistance", type date}, {"Type of Assistance", type text}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date of Assistance]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"Date of Assistance"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns") in #"Removed Duplicates"2. Duplicate to have identical Table (2)
3. Merge, filter and group
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyAjJDKgtSDZVidSASRqgSRnAJY1w6TFAljMESRrjsMMK0A6LDGNMoE7iEKaqEKVjCBJcdJrj8YYLpD6BELAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, #"Date of Assistance" = _t, #"Type of Assistance" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Date of Assistance", type date}, {"Type of Assistance", type text}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date of Assistance]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"Date of Assistance"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns"), #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"ClientID", "Year"}, #"Table (2)", {"ClientID", "Year"}, "Table (2)", JoinKind.LeftOuter), #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Type of Assistance"}, {"Type of Assistance.1"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Table (2)", each [Type of Assistance] < [Type of Assistance.1] ), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Type of Assistance", "Year", "Type of Assistance.1"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Merged Columns" = Table.CombineColumns(#"Grouped Rows",{"Type of Assistance", "Type of Assistance.1"},Combiner.CombineTextByDelimiter(" and ", QuoteStyle.None),"Merged") in #"Merged Columns"I hope this helps!
