cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djworsdell
Frequent Visitor

Struggling to filter gallery with multiple criteria (combinations of IF, AND, OR) + search

Hey all,

 

Bit of a brain teaser for everyone... struggling to filter a Canvas App gallery within Power Apps that requires multiple nested criteria (a combination of IF, AND, + OR statements) plus search functionality.

 

I have four pieces of criteria which I need to pick and choose from...

  • Category - 1, 2, 3, and 4

  • Status - 1, 2, 3, and 4

  • Complete - True or False

  • Enabled - True or False

There are rules associated with

  • Category 1 and Status 1 must ALWAYS be shown

  • As Status increases, the previous 'levels' of Status must be shown

    • (i.e., if Status = 4, show Status 1, 2, 3, and 4)

I've found that my current solution will show most of what I need - but - given the right circumstances, the multiple IF / AND statements will complete and only show half of what is required. Here's what I currently have...

 

Filter(
    // Get source and sort by 'sort_order'
    SortByColumns("sort_order", Descending),
    
    // Category = 1, Status = 1
    category = 1 &&
    status = 1 &&
    completed = 0 &&
    enabled = 1

    // Category = 2 and / or 3, Status = 2, 3, 4
    && If(status = 4,
        (status = 1 || status = 2 || status = 3 || status = 4) &&
        (category = 2 || category = 3) &&
        area = 1 &&
        completed = 0 &&
        enabled = 1,
    If(status = 3,
        (status = 1 || status = 2 || status = 3) &&
        (category = 2 || category = 3) &&
        area = 1 &&
        completed = 0 &&
        enabled = 1,
    If(status = 2,
        (status = 1 || status = 2) &&
        (category = 2 || category = 3) &&
        area = 1 &&
        completed = 0 &&
        enabled = 1)

    //  Category = 4, Status = 2, 3, 4
    && If(status = 4,
        (status = 1 || status = 2 || status = 3 || status = 4) &&
        category = 4 &&
        area = 1 &&
        completed = 0 &&
        enabled = 1,
    If(status = 3,
        (status = 1 || status = 2 || status = 3) &&
        category = 4 &&
        area = 1 &&
        completed = 0 &&
        enabled = 1,
    If(status = 2,
        (status = 1 || status = 2) &&
        category = 4 &&
        area = 1 &&
        completed = 0 &&
        enabled = 1)
)))))

If anyone has any ideas, I would be INCREDIBLY grateful. Thank you all in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks @Sher0024, in the end, I combined your suggestion and ClearCollect() as otherwise the logic of filtering through several layers of business rules just wouldn't work.

View solution in original post

2 REPLIES 2
Sher0024
Helper I
Helper I

Hello,

Not sure if this is what you're looking for:


Filter('DataSource', And( Status_FilterSelection <= Status_DataSource, Category_FilterSelection <= Category_DataSource, area = 1, completed = 0 , enabled = 1 ))

Thanks @Sher0024, in the end, I combined your suggestion and ClearCollect() as otherwise the logic of filtering through several layers of business rules just wouldn't work.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,343)