cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShiroKame
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
v-jefferni
Community Support
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.

View solution in original post

6 REPLIES 6
Nogueira1306
Super User
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 you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

zmansuri
Super User
Super User

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

 

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

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

It doesn't work 😞

v-jefferni
Community Support
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.

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

 

 

 

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.

Top Solution Authors
Users online (3,606)