cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clara100
Frequent Visitor

How to change condition (AND / OR) of a slicers

Hello,

When I do a multiple selection in a slicer, I would choose the condition (AND / OR) for that selection. How can I do?

 

Example:

 

I have this filter: 

 

Slicer
A
B
C
D
E

 

And this table: 

 

CodeAtrtibute
Code1A
Code2B
Code3A
Code4C
Code5C
Code6A
Code1B
Code2B
Code3E
Code1D
Code2A
Code3C
Code4B
Code5E
Code6A

 

If I select "A"  "B" from the slicer, than I would choose the condition "AND" (having code1, code2, code3, code6) or the condition "OR" (having code1, code2).

 

Thank you.

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

Hi @clara100 ,

In your scenario, we can create another table with values "AND", "OR" to create a slicer, then create a distinct table with the Distinct() function, then we cna create a measure to judge it:

Measure = var a = CONCATENATEX(FILTER(ALL('Table'),'Table'[Code] = MIN('Table'[Code])),'Table'[Atrtibute],"")
var b = IF(MIN('Table'[Atrtibute]) in ALLSELECTED(Table2[Slicer]),1,0)
var c = IF(a = CONCATENATEX(ALLSELECTED(Table2[Slicer]),Table2[Slicer],""),1,0)
return IF(SELECTEDVALUE(Table3[Column1]) = "AND", c,b)

By the way, the logic of your "AND", "OR" is strange, generally, when I select "OR" and "AB", it should return 1,2,3,4,6.

PBIDesktop_hglIGxqD58.png

Best Regards,

Teige

View solution in original post

3 REPLIES 3
TeigeGao
Solution Sage
Solution Sage

Hi @clara100 ,

In your scenario, we can create another table with values "AND", "OR" to create a slicer, then create a distinct table with the Distinct() function, then we cna create a measure to judge it:

Measure = var a = CONCATENATEX(FILTER(ALL('Table'),'Table'[Code] = MIN('Table'[Code])),'Table'[Atrtibute],"")
var b = IF(MIN('Table'[Atrtibute]) in ALLSELECTED(Table2[Slicer]),1,0)
var c = IF(a = CONCATENATEX(ALLSELECTED(Table2[Slicer]),Table2[Slicer],""),1,0)
return IF(SELECTEDVALUE(Table3[Column1]) = "AND", c,b)

By the way, the logic of your "AND", "OR" is strange, generally, when I select "OR" and "AB", it should return 1,2,3,4,6.

PBIDesktop_hglIGxqD58.png

Best Regards,

Teige

View solution in original post

Hi @TeigeGao, thank you for the reply.

 

You're right, I wrote a wrong thing. I would say: If I select "AB" from the slicer, then I select "AND" it should return code1, code2 while selecting "OR" it should return code1, code2, code3, code4, code6.

 

By the way, I can't replicate your indications.

I think I'm missing something.

 

So, I have: Table (Code, Attribute) ; Table2 (Slicer) - that has unique values of the column attribute- ; Table3 (Slicer Option)

 

CodeAtrtibute
Code1A
Code2B
Code3A
Code4C
Code5C
Code6A
Code1B
Code2B
Code3E
Code1D
Code2A
Code3C
Code4B
Code5E
Code6A

 

Slicer
A
B
C
D
E

 

Slicer Option
AND
OR

 

Then I created the measure as you suggest:

 

Measure = var a = CONCATENATEX(FILTER(ALL('Table');'Table'[Code] = MIN('Table'[Code]));'Table'[Atrtibute];"")
var b = IF(MIN('Table'[Atrtibute]) in ALLSELECTED(Table2[Slicer]);1;0)
var c = IF(a = CONCATENATEX(ALLSELECTED(Table2[Slicer]);Table2[Slicer];"");1;0)
return IF(SELECTEDVALUE(Table3[Slicer Option]) = "AND"; c;b)
 
What I have to do next?
Thank you,
clara100

Hi @clara100 ,

We need to use distinct() function to create a calculated table whose name is "Table", then we can create the measure.

Best Regards,

Teige

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors