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
Super User

@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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
Super User

@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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
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.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,981)