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

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

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

Hey Randy,

 

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (109,783)