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

Filter using multiple column and variable combination

I am trying to filter two columns col1 and col2 using two variables, var1 and var2.

 

Here is an example of the dataset

rowcol1col2

1

var1var2
2var2var1
3randomValuevar1
4randomValuevar2

 

I have 3 cases:

- if var2 is blank but var1 is not:

col1 and col2 are filtered using var1 -> output: row 1, 2 and 3

- if var1 is blank but var2 is not:

col1 and col2 are filtered using var2 -> output: row 1, 2 and 4

- if none of them are blank:

find all combination of var1 and var2 over col1 and col2 -> output: row 1 and 2

 

I am sure this is just a logic issue on my part, but I cannot seem to be able to wrap my head around this...

 

Thank you for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Msag
Frequent Visitor

Hello Jeffer,

 

Thank you for reaching back to me. I ended up finding a solution for that problem, it was just a matter of correctly using the logical operators. Here is my code:

    // case only unit1 filed
    If(
        !IsBlank(varUnit1) && IsBlank(varUnit2),
        (
            'Unit 1'.Value = varUnit1 &&
            If(
                !IsBlank(varMin1),
                'Min 1' >= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Max 1' <= varMax1,
                true
            )
        ) || (
            'Unit 2'.Value = varUnit1 &&
            If(
                !IsBlank(varMin1),
                'Min 2' >= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Max 2' <= varMax1,
                true
            )
        ),
        true
    ),
    // case only unit 2 filed
    If(
        IsBlank(varUnit1) && !IsBlank(varUnit2),
        (
            'Unit 1'.Value = varUnit2 &&
            If(
                !IsBlank(varMin2),
                'Min 1' >= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Max 1' <= varMax2,
                true
            )
        ) || (
            'Unit 2'.Value = varUnit2 &&
            If(
                !IsBlank(varMin2),
                'Min 2' >= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Max 2' <= varMax2,
                true
            )
        ),
        true
    ),
    // case both of them filled 
    If(
        !IsBlank(varUnit1) && !IsBlank(varUnit2),
        (
            'Unit 1'.Value = varUnit1 && 'Unit 2'.Value = varUnit2 && 
            If(
                !IsBlank(varMin1),
                'Min 1' <= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Min 2' <= varMax1,
                true
            ) &&
            If(
                !IsBlank(varMin2),
                'Max 1' <= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Max 2' <= varMax2,
                true
            ) 
        ) || (
            'Unit 1'.Value = varUnit2 && 'Unit 2'.Value = varUnit1 &&
            If(
                !IsBlank(varMin1),
                'Max 1' <= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Max 2' <= varMax1,
                true
            ) &&
            If(
                !IsBlank(varMin2),
                'Min 1' <= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Min 2' <= varMax2,
                true
            ) 
        )

ColX = varUnitX. Then my minVarX/maxVarX would be varX.

 

It is really lengthy and quite hard to read. If you have tips, I would love to improve that piece of code!

View solution in original post

3 REPLIES 3
Msag
Frequent Visitor

I'm sorry I forgot to specify that both case 1 and 2 (so with a blank variable in the mix) are ok, it is really for the 3rd case

v-jefferni
Community Support
Community Support

Hi @Msag ,

 

The logic for the 3rd case would be Filter the table on row ids in the result rows of case 1 filter AND case 2 filter.

Could you please post your formulas that are working for the cases 1&2?

 

Hope this helps.

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Msag
Frequent Visitor

Hello Jeffer,

 

Thank you for reaching back to me. I ended up finding a solution for that problem, it was just a matter of correctly using the logical operators. Here is my code:

    // case only unit1 filed
    If(
        !IsBlank(varUnit1) && IsBlank(varUnit2),
        (
            'Unit 1'.Value = varUnit1 &&
            If(
                !IsBlank(varMin1),
                'Min 1' >= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Max 1' <= varMax1,
                true
            )
        ) || (
            'Unit 2'.Value = varUnit1 &&
            If(
                !IsBlank(varMin1),
                'Min 2' >= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Max 2' <= varMax1,
                true
            )
        ),
        true
    ),
    // case only unit 2 filed
    If(
        IsBlank(varUnit1) && !IsBlank(varUnit2),
        (
            'Unit 1'.Value = varUnit2 &&
            If(
                !IsBlank(varMin2),
                'Min 1' >= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Max 1' <= varMax2,
                true
            )
        ) || (
            'Unit 2'.Value = varUnit2 &&
            If(
                !IsBlank(varMin2),
                'Min 2' >= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Max 2' <= varMax2,
                true
            )
        ),
        true
    ),
    // case both of them filled 
    If(
        !IsBlank(varUnit1) && !IsBlank(varUnit2),
        (
            'Unit 1'.Value = varUnit1 && 'Unit 2'.Value = varUnit2 && 
            If(
                !IsBlank(varMin1),
                'Min 1' <= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Min 2' <= varMax1,
                true
            ) &&
            If(
                !IsBlank(varMin2),
                'Max 1' <= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Max 2' <= varMax2,
                true
            ) 
        ) || (
            'Unit 1'.Value = varUnit2 && 'Unit 2'.Value = varUnit1 &&
            If(
                !IsBlank(varMin1),
                'Max 1' <= varMin1,
                true
            ) &&
            If(
                !IsBlank(varMax1),
                'Max 2' <= varMax1,
                true
            ) &&
            If(
                !IsBlank(varMin2),
                'Min 1' <= varMin2,
                true
            ) &&
            If(
                !IsBlank(varMax2),
                'Min 2' <= varMax2,
                true
            ) 
        )

ColX = varUnitX. Then my minVarX/maxVarX would be varX.

 

It is really lengthy and quite hard to read. If you have tips, I would love to improve that piece of code!

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.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

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

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (5,964)