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

[djworsdell]

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!

[Sher0024]

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 ))

[djworsdell · Accepted solution]

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.