Group by one column and only select rows where values in another column occur in both lists

[RS1]

I'm looking to find a distinct count of Column 1 where, when grouping by Column 1, values in Column 2 may be found in two lists. 

List 1 = {1, 2}

List 2 = {X, Y}

A	4
A	2
A	X
B	3
B	Z
C	1
C	Y
C	Q
D	2
D	X

Here the expected count would be 3, because:

Where Column 1 = A, Column 2 contains values {4, 2, X}. Since 2 is in {1, 2} and X is in {X, Y}, the criteria is met

Where Column 1 = C, Column 2 contains values {1, Y, Q}. Since 1 is in {1, 2} and Y is in {X, Y}, the criteria is met

Where Column 1 = D, Column 2 contains values {2, X}. Since 2 is in {1, 2} and X is in {X, Y}, the criteria is met

I'm not sure how to create a measure in Power BI that accomplishes this. Any help would be greatly appreciated!

[Anonymous]

Hi @RS1 

You can refer to the following measure

Sample data 

Measure = var _list1= {1, 2}
var _list2={"X","Y"}
var _conv=CONCATENATEX(FILTER(ALLSELECTED('Table'),[Type1] in VALUES('Table'[Type1])),[Type2],",")
return IF(COUNTROWS(FILTER(_list1,CONTAINSSTRING(_conv,[Value])))>0&&COUNTROWS(FILTER(_list2,CONTAINSSTRING(_conv,[Value])))>0,"meet","false")

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Why would that need to be a measure?

Here's a Power Query version

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJRitWBsIzgrAgwywnIMoazosAsZyDLEM6KhLMCwSwXuCkuEFNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Test", each List.Contains({"1","2"},[Column2]) or List.Contains({"X","Y"},[Column2])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Test] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"