cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
morall
Microsoft
Microsoft

Help writing the logic to handle multiple if and filter statements

I have all of these different filters that a user has access to. I'm trying to write the logic in the items section of the gallery but it's becoming pretty gnarly.

 

These are all of the filters:

Screen Shot 2020-07-11 at 11.34.18 AM.png

 

This works for handling the search bar, etc. 

 

 

 

Sort(
    If(
        And('Already Viewed Toggle'.Value=true, Radio1.Selected.Value = "HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested > 0, 
                'iCIMS ID' in viewed_profiles,
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
        And('Already Viewed Toggle'.Value=true, Radio1.Selected.Value = "w/o HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested = 0, 
                'iCIMS ID' in viewed_profiles, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
        And('Already Viewed Toggle'.Value=false, Radio1.Selected.Value = "HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested > 0, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
        And('Already Viewed Toggle'.Value=false, Radio1.Selected.Value = "w/o HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested = 0, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
            Filter(
                pipeline_candidates, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name))
    ),
    'Created On',
    Descending
)

 

 

 

But I'm having to write every permutation in the If statement, which is creating a headache when I now want to add the ability to filter on years of experience:

 

 

 

    If(
        'Yrs of Exp drpdown'.Selected.Value = "All", 
            Filter(
                'Pipeline Candidates', 'Years of experience' > 0
            ), 
        'Yrs of Exp drpdown'.Selected.Value = "2-5 yrs", 
            Filter(
                'Pipeline Candidates', 
                'Years of experience' >= 2,
                'Years of experience' <= 5
            ),
        'Yrs of Exp drpdown'.Selected.Value = "6-9 yrs", 
            Filter(
                'Pipeline Candidates', 
                'Years of experience' >= 6,
                'Years of experience' <= 9
            ),
        'Yrs of Exp drpdown'.Selected.Value = "10+ yrs", 
            Filter(
                'Pipeline Candidates', 
                'Years of experience' >= 10
            )
    ), 

 

 

 

Any thoughts on how I can clean this up? 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@morall 

Consider revisiting your formula and simplifying.  It will be much easier...

 

SortByColunmns(
    Filter(pipeline_candidates,
         
        If('Already Viewed Toggle'.Value, 
           iCIMS ID' in viewed_profiles, 
           true
        ) &&

        Switch(Radio1.Selected.Value,
            "HM Interest", CountHMIntested > 0, 
            "w/o HM Interest", CountHMIntested = 0,
            true
        ) &&
        
        Switch('Yrs of Exp drpdown'.Selected.Value,            
            "2-5 yrs", 'Years of experience' >= 2 && 'Years of experience' <= 5,
            "2-5 yrs", 'Years of experience' >= 6 && 'Years of experience' <= 9, 
            "10+ yrs", 'Years of experience' >= 10,
            true
        ) &&
           
        search_box.Text in Name &&
        PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result
    ),
    'Created On',
    Descending
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User III
Super User III

@morall 

Consider revisiting your formula and simplifying.  It will be much easier...

 

SortByColunmns(
    Filter(pipeline_candidates,
         
        If('Already Viewed Toggle'.Value, 
           iCIMS ID' in viewed_profiles, 
           true
        ) &&

        Switch(Radio1.Selected.Value,
            "HM Interest", CountHMIntested > 0, 
            "w/o HM Interest", CountHMIntested = 0,
            true
        ) &&
        
        Switch('Yrs of Exp drpdown'.Selected.Value,            
            "2-5 yrs", 'Years of experience' >= 2 && 'Years of experience' <= 5,
            "2-5 yrs", 'Years of experience' >= 6 && 'Years of experience' <= 9, 
            "10+ yrs", 'Years of experience' >= 10,
            true
        ) &&
           
        search_box.Text in Name &&
        PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result
    ),
    'Created On',
    Descending
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

yes, this is amazing. Thank you.

Hey @RandyHayes ,

 

This is really helpful in understanding how to have multiple If statements. I'm trying to add one of my own and would appreciate your input.

 

This is a multi-select input:

 

Screen Shot 2020-07-12 at 2.14.01 PM.png

I'm trying to incorporate this, where I have the Filter() inside the If statement. If the dropdown is selected to "All", show everyone, otherwise Filter(). I'm a little confused as to how I can write this with the Filter outside the If statement

 

 

Sort(
    If(
        drpLocation.Selected.Value = "All", pipeline_candidates,
        Filter(pipeline_candidates, drpLocation.Selected.Value in Concat('Desired Location'.Value, Value & ", "))
    ),
    'Created On',
    Descending
)

 

 

I've been trying a few different ways, trying both If() and Switch() but can't get it to work. Any thoughts? 

@morall 

Give this a shot...

Sort(
   Filter(
          pipeline_candidates, 
          If(drpLocation.Selected.Value = "All", 
             true,
             drpLocation.Selected.Value in Concat('Desired Location'.Value, Value & ", ")
          )
    ),
    'Created On',
    Descending
)

 

Thing to remember with filter is that it has a condition.  If any of the condition evaluates to "true", then the row will be part of the results.  

So, in the above, if the dropdown has "All" in it, then the result of the If will be "true" (meaning every row will be a true returned result).  If the dropdown has something else, then it is up to other part (the "in" operator) to determine true or false.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hey Randy,

 

That worked! Great explanation, that makes sense. Thank you! 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

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

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,823)