cancel
Showing results for
Search instead for
Did you mean:
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

 row col1 col2 1 var1 var2 2 var2 var1 3 randomValue var1 4 randomValue var2

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

3 REPLIES 3
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

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.

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

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.

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.

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.

Top Solution Authors
Top Kudoed Authors
Users online (5,964)