Filter using multiple column and variable combination

(0) Share

Report

Posted on by Msag

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Msag 23 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Msag 23 on at

Like (0)

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

Was this reply helpful? Yes No