cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kelbow Frequent Visitor
Frequent Visitor

row filter with multiple clauses

Hi,

I'm building a dashboard with a data set where I want to filter the rows down by multiple OR clauses. It appears to do so, however, when I apply the filter, it only retains the last two clauses. Is this a known restriction?

 

Here's what I get;

= Table.SelectRows(#"Renamed Columns", each [OwnerLogin] = "officedev" or [OwnerLogin] = "onenotedev" or [OwnerLogin] = "onedrive" or [OwnerLogin] = "microsoftgraph" or [FullRepoName] = "Microsoft/Resource-Static-Analysis")

 

But generally speakig, the result only gives me rows filtered to the last two:

[OwnerLogin] = "microsoftgraph" or [FullRepoName] = "Microsoft/Resource-Static-Analysis"

 

Suggestions?

12 REPLIES 12
Super User
Super User

Re: row filter with multiple clauses

@kelbow It apperas you're filtering via query editor. Instead I would suggest you import all then filter via page-level or report-level filter.

Community Support Team
Community Support Team

Re: row filter with multiple clauses

Hi kelbow,

 

I agree with ankitpatira’s point of view, it seems more suitable than use query edit.

 

Based on test, I can’t reproduce your issue, it works well, can you provide more info about this operation? Before you add filters, please check if there are records meet [OwnerLogin] = "officedev" or [OwnerLogin] = "onenotedev" or [OwnerLogin] = "onedrive".

 

Test sample

Power query formula:

let

    Source = OData.Feed("services.odata.org/V4/Northwind/Northwind.svc"),

    Employees_table = Source{[Name="Employees",Signature="table"]}[Data],

    #"Filtered Rows" = Table.SelectRows(Employees_table, 

    each [City] = "London" 

    or [City] = "Redmond" 

    or [City] = "Tacoma" 

    or [City] = "Seattle" 

    or [Region] = "WA")

in

#"Filtered Rows"

 

 Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
kelbow Frequent Visitor
Frequent Visitor

Re: row filter with multiple clauses

I can confirm that there are records that meet those criteria. Why is a report or page level filter more appropos in this case? I'd like to understand the rationale so I can make the right choice in the future. What are the benfits of choosing one approach over another?

 

 

kelbow Frequent Visitor
Frequent Visitor

Re: row filter with multiple clauses

OK. So the other problem I have with your suggested solution is that I have a complex filter...

 

I want rows where

ownerlogin = X

OR

ownerlogin = Y

OR

ownerlogin = Z

OR

repoName = ZZ

 

I can't determine how to do that as part of a report or page level filter.  When I try to use the report or page-level filter, using the Advanced tab, I can only choose two criteria and I need to choose four criteria. Also, I can do the first filters but not the last one because it's a different dimension...

Community Support Team
Community Support Team

Re: row filter with multiple clauses

Hi kelbow,

 

>>I can't determine how to do that as part of a report or page level filter.  When I try to use the report or page-level filter, using the Advanced tab, I can only choose two criteria and I need to choose four criteria.

 

Based on test, the ‘Advanced mode’ only support two option,  you can use the ‘base mode’ to select the specify records. But the page-level filter or report-level filter use ‘and mode’ to link, for example (filter1) and (filter2) not the ‘or mode’.

 

Since page-level filter or report-level filter not suitable for your requirement, you could use dax filter function or power query selectrows function to achieve your requirement.

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
kelbow Frequent Visitor
Frequent Visitor

Re: row filter with multiple clauses

So feedback - the advanced mode should support more than two options. For me, and the number of values in my list, filtering through the base mode takes me more than 15 minutes to select my values since the only option is scrolling and not typing.

 

Is there a link to a topic or demo that shows how to use the dax filter function or power query select rows function?

Highlighted
Community Support Team
Community Support Team

Re: row filter with multiple clauses

Hi @kelbow,

 

>>Is there a link to a topic or demo that shows how to use the dax filter function or power query select rows function?

You can take a look at below links:

 

Filter Functions (DAX)

 

Filter a table (Power Query)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
kelbow Frequent Visitor
Frequent Visitor

Re: row filter with multiple clauses

Thanks. So...while I appreciate the pointer to those things, I guess that's not really my question. Where would I do that in PowerBI? The first topic gives me the syntax for filtering - which is nice...but literally - where would I do that in the PowerBI UI? The second topic, again, talks through how to do that with Power Query but, where do I find Power Query in PowerBI? Where would I do this in the UI on PBI desktop? Do I do this in the query editor? If so, is this a step I'm adding? I believe that's what I'm doing and I'm encountering this bug.

Community Support Team
Community Support Team

Re: row filter with multiple clauses

Hi @kelbow,

 

About dax formula, you can use like this:

 

Filtered Table= CALCULATETABLE(Table,FILTER(Table,Table[Column1]="A"||Table[Column1]="B"||Table[Column1]="C"||Table[Column2]="D"))

 

For Power query filter multiple columns, you can follow below steps:

 

1. Open query editor and click the column's drop down button to open the menu.

Capture.PNG

 

2. Choose one option which belowe the filter to open the filter rows panel, select the "advanced" mode.

Capture2.PNG

 

3. Add the filter conditions.

Capture3.PNG

 

4. Click ok to general the power query formula, you can check it by open the advanced editor.

Capture4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |