cancel
Showing results for
Did you mean:
Frequent Visitor

## Does not include Filter condition based on another condition

Hi,

My Sharepoint List have a Yes/No column, and in List screen, I have a checkbox for this Yes/No column.

If User check the checkbox, the gallery will return True value of Yes/No column, and if User uncheck the checkbox, ALL items will show.

I am currently using below filter

``````If(checkBox.Value,
Filter(TableA, YesNoColumn.value = true, Condition A, Condition B, Condition C),
Filter(TableA, Condition A, Condition B, Condition C))``````

↑ The problem is 2 have more than 2 Yes/No column to deal with, so the code for 2 Yes/No column is

``````If(CheckBox1.Value,
//Checkbox 1 true, include Condition C
If(CheckBox2.Value,
//checkbox 2 true, return True value
Filter(TableA, YesNoColumn1.value = true, Condition A, Condition B, Condition C),
//checkbox 2 false, return All value
Filter(TableA, Condition A, Condition B, Condition C)),
//Checkbox 1 false, does not include Condition C
If(CheckBox2.Value,
//checkbox 2 true, return True value
Filter(TableA, YesNoColumn1.value = true, Condition A, Condition B),
//checkbox 2 false, return All value
Filter(TableA, Condition A, Condition B))``````

And as you see above, the maintenance effort for the code is HUGE.

Do you have any suggestion?

Thanks so much!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @ShiroKame ,

So this is a logical test optimization problem. Let's sort out the logic.

You have a Yes/No column, 2 Check boxes out of the Gallery, and would like to filter the table by the Yes/No column value and other 3 conditions. One Check box bonds to the Yes/No column value and the other bonds to condition C. No matter how you check the 2 Check boxes condition A & B will always as the filter conditions. So, try below formula:

``````Filter(
TableA,
ConditionA
&&
ConditionB
&&
//CheckBox1 check, return ConditionC, otherwise returen true
If(CheckBox1.Value, ConditionC, true)
&&
//CheckBox2 check, return YesNoColumn ture, otherwise returen true
If(Checkbox2.Value, YesNoColumn.Value = true, true)
)``````

This is the simplest way for the two check boxes. But you will see delegation warning in the formula since Yes/No column is non-delegable. So we will have to filter by the three conditions first, then filter the result table by the Yes/no column:

``````With(
{Results:
Filter(
TableA,
ConditionA
&&
ConditionB
&&
//CheckBox1 check, return ConditionC, otherwise returen true
If(CheckBox1.Value, ConditionC, true)
)
},
Filter(
Results,
//CheckBox2 check, return YesNoColumn ture, otherwise returen true
If(Checkbox2.Value, YesNoColumn.Value = true, true)
)
)``````

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

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

6 REPLIES 6
Super User

Hey

The code for the 2 Yes/No collumns should be:

``````If(CheckBox1.Value = false & CheckBox2.Value = false,
//Condition for both false  ,
If(CheckBox1.Value = true & CheckBox2.Value = false,
//Condition for 1 true and 2 false   ,
If(CheckBox1.Value = false & CheckBox2.Value = true,
//Condition for 1 false and 2 true    ,
//Condition for both true
)
)
)``````

If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my User Group (pt-PT)!

Super User

I have never tried this. But can you try this?

Filter(TableA, YesNoColumn.Value= checkBox.Value, Condition A, Condition B, Condition C)

Frequent Visitor

The number of condition is the same, 4 conditions, so I think the code maintenance effort is the same.

Frequent Visitor

It doesn't work 😞

Community Support

Hi @ShiroKame ,

So this is a logical test optimization problem. Let's sort out the logic.

You have a Yes/No column, 2 Check boxes out of the Gallery, and would like to filter the table by the Yes/No column value and other 3 conditions. One Check box bonds to the Yes/No column value and the other bonds to condition C. No matter how you check the 2 Check boxes condition A & B will always as the filter conditions. So, try below formula:

``````Filter(
TableA,
ConditionA
&&
ConditionB
&&
//CheckBox1 check, return ConditionC, otherwise returen true
If(CheckBox1.Value, ConditionC, true)
&&
//CheckBox2 check, return YesNoColumn ture, otherwise returen true
If(Checkbox2.Value, YesNoColumn.Value = true, true)
)``````

This is the simplest way for the two check boxes. But you will see delegation warning in the formula since Yes/No column is non-delegable. So we will have to filter by the three conditions first, then filter the result table by the Yes/no column:

``````With(
{Results:
Filter(
TableA,
ConditionA
&&
ConditionB
&&
//CheckBox1 check, return ConditionC, otherwise returen true
If(CheckBox1.Value, ConditionC, true)
)
},
Filter(
Results,
//CheckBox2 check, return YesNoColumn ture, otherwise returen true
If(Checkbox2.Value, YesNoColumn.Value = true, true)
)
)``````

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

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

Dear Jeffer,

Brilliant!!!!

My code reduce as 1/4 compared to the original.

Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!

I have never used With function before, and now I have learnt it from your code!!!!!

Announcements

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 (3,606)