'AND' filtering a property in reports

[jPinhao]

The default filtering for a given property is an 'OR' filter, for both Slicers and Filters.

eg. If I create a slicer for 'Product', and select both 'Product A' and 'Product B', the applied filter will be 'Product = "Product A" OR Product = "Product B"'.

What I'd like is an 'AND' slicer? I understand for most cases this doesn't make much sense, but if you have a many-to-many relation (eg. Store -> Product, where a store can have many products, and a product can be available in many stores) it becomes valuable.

eg. Create a slicer for Product, select "Product A" and "Product B", and the report would show you stores where both "Product A" and "Product B" are available, rather than stores where either of them is available.

Is there any way to achieve this?

[Anonymous · Accepted solution]

Hi @jPinhao,

I think and filter affect also not suitable for your requirement. Power bi slicer is filter the row content, if the and filter affect enable, you will get blank result.

I have an idea to work around your requirement, you can take a look at below steps if it suitable for your requirement.

1. Use slicer to get the records which contain the chosen products.
2. Write a measure to calculate the distinct count of each types, return a flag if the distinct count equal to the selected items count of slicer.
3. Drag above measure to visual and enable the visual level filter to remove the records which not exist the flag.

Sample:

Measures.

Get the count of selected items in slicer.

SelectedCount = COUNTROWS(ALLSELECTED(Sheet2[Type]))

Add flag:

IsContainAll = 
var temp = LASTNONBLANK(Table[Store],[Store])
var distinctNumber= CALCULATE(DISTINCTCOUNT(Table[Product]),FILTER(ALLSELECTED(Table),[Store]=temp))
return
if(distinctNumber=[SelectedCount],"Y","N")

Regards,

Xiaoxin Sheng