Forum Discussion
Removing rows based on 1++ criteria
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 |
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.
3 Replies
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.
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" ) )Thanks! I just remembered a SWITCH statement could be used here as well, and would require lesser indentations code-wise.
