Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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. Aggregate
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")
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!
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. Aggregate
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")
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!
Hi @alena2k, it took me a while to understand the instructions but I finally did get to the point where I have similar results that you display in 2.png above. I will now take this and attempt to apply it to the actual application table that I have. thanks much!
hi @jwessel, I am happy to hear that! Good luck with completing your task, let me know if you have any questions.
Hi @alena2k , I am not sure I am following and apologies for my newbieness. Do I use that first set of code as part of a creation of a new table to duplicate the original table?
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
Hi @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.
Hi @Shaurya
I will try this out first and see what results I come up with. My sample table did not give the whole table that I'm actually looking at. I may have to post the whole .pbix. I will get back to this post. Thanks much!