cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srduval
Kudo Collector
Kudo Collector

Filter() Using an "OR" in the expression

I have the below expression that I am trying to use in filtering the contents of a sharepoint list. I want to be able to filter the list to only show the systems needed for the job title specified or the job title equals All. I'm getting the results for where the job title matches, but not getting results where the job title is All, what am I doing wrong? 

 

Filter(GroupBy(Systems_For_All_Staff_Positions,"Title","Department","field_What_x0020_System_x0020_Does","field_Job_x0020_Title","field_Contact_x0020_Person","field_Example_x0020_","DistinctSystems"),Or(field_Job_x0020_Title=Param("JobTitle"),field_Job_x0020_Title="All")=true)

 

I have also tried 

Filter(GroupBy(Systems_For_All_Staff_Positions,"Title","Department","field_What_x0020_System_x0020_Does","field_Job_x0020_Title","field_Contact_x0020_Person","field_Example_x0020_","DistinctSystems"),Or(field_Job_x0020_Title=Param("JobTitle")||"All")

 

With no luck.

1 ACCEPTED SOLUTION

Accepted Solutions

@WarrenBelz My apologies for the delay and incomplete response earlier. What I was trying to elude to was that the code was working, but the value I was expecting to see for Department = "All" was not showing up when I pulled up a record with all the appropriate parameter values in place. It only returned the values that were assigned to the department in question, seemingly ignoring the "OR ALL" clause.


After countless iterations and experiments with the code, I randomly had the idea to try and change up the order of events, grouping a filtered source vs filtering a grouped source. No idea why it matters (record count limit maybe??) But this is the code I am using currently that is giving me the systems for the requested department, as well as anything assigned a value of "ALL"

 

If(
    Param("DepartmentOrJobTitle") = "JobTitle",
    GroupBy(
        Filter(
            Systems_For_All_Staff_Positions,
            field_Job_x0020_Title = "All" Or field_Job_x0020_Title = Param("JobTitle")
        ),
        "Title",
        "Department",
        "field_What_x0020_System_x0020_Does",
        "field_Job_x0020_Title",
        "field_Contact_x0020_Person",
        "field_Example_x0020_",
        "DistinctSystems"
    ),
    GroupBy(
        Filter(
            Systems_For_All_Staff_Positions,
            Department = "All" Or Department = Param("Department")
        ),
        "Title",
        "Department",
        "field_What_x0020_System_x0020_Does",
        "field_Contact_x0020_Person",
        "field_Example_x0020_",
        "DistinctSystems"
    )
)

View solution in original post

5 REPLIES 5
WarrenBelz
Super User
Super User

Hi @srduval ,'

Try this

Filter(
   GroupBy(
      Systems_For_All_Staff_Positions,
      "Title",
      "Department",
      "field_What_x0020_System_x0020_Does",
      "field_Job_x0020_Title",
      "field_Contact_x0020_Person",
      "field_Example_x0020_",
      "DistinctSystems"
   ),
   field_Job_x0020_Title="All" || field_Job_x0020_Title=Param("JobTitle")
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Thanks for trying @WarrenBelz , unfortunately it doesn't seem to do the trick. Unless I'm missing something:
Filter(GroupBy(Systems_For_All_Staff_Positions
,"Title"
,"Department"
,"field_What_x0020_System_x0020_Does"
,"field_Job_x0020_Title"
,"field_Contact_x0020_Person"
,"field_Example_x0020_"
,"DistinctSystems"),field_Job_x0020_Title="All" || field_Job_x0020_Title=Param("JobTitle"))

This is taken out of a larger chunk of code, not sure if it makes a difference (it shouldn't but you never know...)

 

If(Param("DepartmentOrJobTitle")="JobTitle",
Filter(GroupBy(Systems_For_All_Staff_Positions
,"Title"
,"Department"
,"field_What_x0020_System_x0020_Does"
,"field_Job_x0020_Title"
,"field_Contact_x0020_Person"
,"field_Example_x0020_"
,"DistinctSystems"),field_Job_x0020_Title="All" || field_Job_x0020_Title=Param("JobTitle")),
Filter(GroupBy(Systems_For_All_Staff_Positions
,"Title"
,"Department"
,"field_What_x0020_System_x0020_Does"
,"field_Job_x0020_Title"
,"field_Contact_x0020_Person"
,"field_Example_x0020_"
,"DistinctSystems"),Department="All" || Department=Param("Department"))
 

Hi @srduval ,

It is helpful to tell me what is wrong (is the code valid - if so what results does it return etc) - assuming: -

  • You have three incoming parameters - JobTitle, Department and DepartmentOrJobTitle.
  • If the Parameter DepartmentOrJobTitle is "JobTitle", you want all the records with a field_Job _Title field value of the Parameter JobTitle OR a value of "All"
  • If the parameter DepartmentOrJobTitle is not this value, you want all records with the Department field value of the incoming parameter Department OR a value of "All".

If so

Filter(
   GroupBy(
      Systems_For_All_Staff_Positions,
      "Title",
      "Department",
      "field_What_x0020_System_x0020_Does",
      "field_Job_x0020_Title",
      "field_Contact_x0020_Person",
      "field_Example_x0020_",
      "DistinctSystems"
   ),
   If(
      Param("DepartmentOrJobTitle")="JobTitle",
      field_Job_x0020_Title = "All" || field_Job_x0020_Title=Param("JobTitle"),
      Department="All" || Department=Param("Department")
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

@WarrenBelz My apologies for the delay and incomplete response earlier. What I was trying to elude to was that the code was working, but the value I was expecting to see for Department = "All" was not showing up when I pulled up a record with all the appropriate parameter values in place. It only returned the values that were assigned to the department in question, seemingly ignoring the "OR ALL" clause.


After countless iterations and experiments with the code, I randomly had the idea to try and change up the order of events, grouping a filtered source vs filtering a grouped source. No idea why it matters (record count limit maybe??) But this is the code I am using currently that is giving me the systems for the requested department, as well as anything assigned a value of "ALL"

 

If(
    Param("DepartmentOrJobTitle") = "JobTitle",
    GroupBy(
        Filter(
            Systems_For_All_Staff_Positions,
            field_Job_x0020_Title = "All" Or field_Job_x0020_Title = Param("JobTitle")
        ),
        "Title",
        "Department",
        "field_What_x0020_System_x0020_Does",
        "field_Job_x0020_Title",
        "field_Contact_x0020_Person",
        "field_Example_x0020_",
        "DistinctSystems"
    ),
    GroupBy(
        Filter(
            Systems_For_All_Staff_Positions,
            Department = "All" Or Department = Param("Department")
        ),
        "Title",
        "Department",
        "field_What_x0020_System_x0020_Does",
        "field_Contact_x0020_Person",
        "field_Example_x0020_",
        "DistinctSystems"
    )
)
srduval
Kudo Collector
Kudo Collector

@WarrenBelz Your help was much appreciated in confirming that I could use a simple or/|| keyword to split up my filter statement googling wasn't being very decisive

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (2,993)