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)

Sort by

Msag 23 on at

Like (0)

Report
Copy link

Link copied!

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 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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
Copy link

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