Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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....
  • Anonymous's avatar
    Anonymous
    9 years ago

    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"

     

     

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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"

 

 

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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...

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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