Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to 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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |