Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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?
Solved! Go to 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
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.