Filter drop down using AND function

[im_92]

Hi all, 

When using the slicer filter (displayed as drop-down), I would like it to use an AND function when selected multiple items. 

Basically, I want to be able to filter patients based on the comorbidities they have. So when I select 2 or more items in the comorbidity drop down list, I want the report to show the characteristics of the patients that have both/all comorbidities, and not only one of them (OR function; as it works now). Is there any way to do this? 

Thanks! 

[v-deddai1-msft]

Hi @im_92 ,

The default behaviour of the slicer in Power BI is that it shows the result of OR when you select multiple items. If you want to having AND condition using a slicer in Power BI you should use measure to control it.

Here is a good article for your reference: https://radacad.com/slicer-with-and-condition-in-power-bi and if you want the  exact formula, please show your sample data.

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

Best Regards,

Dedmon Dai

[amitchandak]

@im_92 , Creat a measure like

measure =
var _max = countx(allselcted(Table,table[comorbidities]))
return
countx(filter(summarize(Table, Table[patients], "_1",distinctcount(table[comorbidities])),[_1]=_max),[patients])

[S_JB]

If you go to the formatting options for your slicer and go into selection controls,  turn single select off and multi-select with CTRL off and turn Show "select all" on.