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.
I'm doing this manually on the Excel source file, but I think it could be done natively on Power BI. I looked at the example here but it seems to just select a subset of the data based on the conditions. Given the table below, how can I remove all rows of Client C and Type 3 from the dataset? I tried the following DAX code but I'm getting this error:
= Table.SelectRows(#"Replaced Value2", each not Text.Contains([Client] <> "Client C" and [Type] <> "Type 3"))
Expression.Error: 1 arguments were passed to function which expects between 2 and 3. Details: Pattern= Arguments=List
Client | Type | Date |
Client A | Type 1 | 11/20/2017 |
Client B | Type 1 | 12/15/2017 |
Client C | Type 1 | 12/4/2017 |
Client D | Type 1 | 10/24/2017 |
Client A | Type 2 | 7/5/2017 |
Client B | Type 2 | 3/17/2017 |
Client C | Type 2 | 10/2/2017 |
Client D | Type 2 | 6/19/2017 |
Client A | Type 3 | 8/25/2017 |
Client B | Type 3 | 7/17/2017 |
Client C | Type 3 | 6/29/2017 |
Client D | Type 3 | 1/2/2018 |
Client A | Type 4 | 5/25/2017 |
Client B | Type 4 | 6/14/2017 |
Client C | Type 4 | 6/20/2017 |
Client D | Type 4 | 4/21/2017 |
Client A | Type 5 | 8/18/2017 |
Client B | Type 5 | 6/14/2017 |
Client C | Type 5 | 8/17/2017 |
Client D | Type 5 | 6/29/2017 |
Solved! Go to Solution.
Just add a custom column as follows (case sensitive)
= if [Client] = “Client A” and [Type] = “Type 3” then “remove” else null
then put a filter on the new colum to remove the results.
Just add a custom column as follows (case sensitive)
= if [Client] = “Client A” and [Type] = “Type 3” then “remove” else null
then put a filter on the new colum to remove the results.
Thanks! I just remembered a SWITCH statement could be used here as well, and would require lesser indentations code-wise.
Also you can create a new Calculated Table using DAX that filters such rows
Go to Modelling Tab >>New Table
New Table = EXCEPT ( TableName, FILTER ( TableName, TableName[Type] = "Type 3" && TableName[Client] = "Client C" ) )
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |