cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jackroyd
New Member

Make slicers filter using AND condition

I have a scenario where the report users will need to select a number of qualifications from a slicer. this slicer should filter a list of people who have ALL of those qualifications. Right now with slicers I seem to only be able to get the slicer to apply OR with multi select. 

 

So right now if the user selects qualifications A, B, C, anyone with any of those qulaifications  comes up. I only want it to show people with A AND B AND C. 

 

Any help would be great 🙂 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @Jackroyd 

>> So right now if the user selects qualifications A, B, C, anyone with any of those qulaifications  comes up. I only want it to show people with A AND B AND C. 

See example below.

Sample data:

vxiaotang_1-1654153239117.png

at the beginning when you select qualifications A, B, C, anyone with any of those qulaifications  comes up.

vxiaotang_0-1654153211832.png

then create the measure below and put it into visual-level slicer, 

Measure = 
var _t1= ALLSELECTED(Slicer[qulaification])
var _t2=SELECTCOLUMNS( FILTER(ALL('Table'),'Table'[people]=MIN('Table'[people])),"q",[qulaification])
var _t3= INTERSECT(_t1,_t2)
var _count1= COUNTX(_t1,[qulaification])
var _count2= COUNTX(_t3,[qulaification])
return IF( _count1=_count2,1)

result

 

 

vxiaotang_2-1654154342225.png

 

Best Regards,

Community Support Team _Tang

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

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @Jackroyd 

>> So right now if the user selects qualifications A, B, C, anyone with any of those qulaifications  comes up. I only want it to show people with A AND B AND C. 

See example below.

Sample data:

vxiaotang_1-1654153239117.png

at the beginning when you select qualifications A, B, C, anyone with any of those qulaifications  comes up.

vxiaotang_0-1654153211832.png

then create the measure below and put it into visual-level slicer, 

Measure = 
var _t1= ALLSELECTED(Slicer[qulaification])
var _t2=SELECTCOLUMNS( FILTER(ALL('Table'),'Table'[people]=MIN('Table'[people])),"q",[qulaification])
var _t3= INTERSECT(_t1,_t2)
var _count1= COUNTX(_t1,[qulaification])
var _count2= COUNTX(_t3,[qulaification])
return IF( _count1=_count2,1)

result

 

 

vxiaotang_2-1654154342225.png

 

Best Regards,

Community Support Team _Tang

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

nickolajj
Frequent Visitor

Try this:

Basically just counting the amount of selections in the slicer and comparing them to the number of skills of a worker (where the number of skills for a worker is filtered by the slicer, so it should work out)

nickolajj_2-1653912570058.png

 

 

ribisht17
Super User
Super User

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors