How to Filter Rows by Correlating Values in One Column to Another

[kevincoleman]

Example:

BEFORE

AAA	Eggs	0
AAA	Milk	1
BBB	Cookies	0
BBB	Eggs	0
CCC	Milk	1
CCC	Eggs	1

AFTER

CCC	Milk	1
CCC	Eggs	1

Goal: FOR each row with the value '0' in column 3, FILTER out all rows that have the same value in column 1.

(Since Row 1 had a 0 in column 3, all rows with 'AAA' in column 1 were filtered out.) 

(Since Row 2 had a 0 in column 3, all rows with 'BBB' in column 1 were filtered out.)

How can I acheive this using M ?

[Mariusz · Accepted solution]

Hi @kevincoleman 

Please see the M script below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRck1PLwZSBkqxOjAh38ycbCBlCBZycnICsp3z87MzUxEKIaIoep2dndH1QoSgqoBCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column3] = 0)),
    #"Exclude List" = List.Distinct(Table.Column(#"Filtered Rows","Column1")),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each List.Contains(#"Exclude List", [Column1]) = false)
in
    #"Filtered Rows1"

Regards,
Mariusz

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

[d_gosbell]

Or you could do this in a measure. The following measure will return 1 for rows which don't have a 0 against the item in Column1 so you can just add filter to your visual for where this measure is 1.

Measure = 
VAR col1WithZero = CALCULATETABLE(DISTINCT(Table1[Column1]),  FILTER(ALL(Table1), table1[Column3] = 0))
var result = COUNTROWS(FILTER(VALUES(Table1[Column1]), NOT Table1[Column1] IN col1WithZero))
return result