Summarize by two different field values grouping by another field

[jwessel]

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

[alena2k · Accepted 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!

[jwessel]

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!

[alena2k]

hi @jwessel, I am happy to hear that! Good luck with completing your task, let me know if you have any questions.

[jwessel]

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?

[Shaurya]

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

[jwessel]

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.

[jwessel]

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!