Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 @Anonymous 

>> 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 @Anonymous 

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

Anonymous
Not applicable

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

@Anonymous 

 

Do you mean like this >> Solved: Filter with And Condition using slicer - Microsoft Power BI Community

 

Regards,

Ritesh

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.