cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
jPinhao
Helper II
Helper II

'AND' filtering a property in reports

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?

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors