cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

 

ClientTypeDate
Client AType 111/20/2017
Client BType 112/15/2017
Client CType 112/4/2017
Client DType 110/24/2017
Client AType 27/5/2017
Client BType 23/17/2017
Client CType 210/2/2017
Client DType 26/19/2017
Client AType 38/25/2017
Client BType 37/17/2017
Client CType 36/29/2017
Client DType 31/2/2018
Client AType 45/25/2017
Client BType 46/14/2017
Client CType 46/20/2017
Client DType 44/21/2017
Client AType 58/18/2017
Client BType 56/14/2017
Client CType 58/17/2017
Client DType 56/29/2017
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Removing rows based on 1++ criteria

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3
Highlighted

Re: Removing rows based on 1++ criteria

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Highlighted
Super User III
Super User III

Re: Removing rows based on 1++ criteria

@olimilo

 

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"
    )
)
Try my new Power BI game Cross the River
Highlighted
Helper V
Helper V

Re: Removing rows based on 1++ criteria

Thanks! I just remembered a SWITCH statement could be used here as well, and would require lesser indentations code-wise.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors