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
Nickodemus
Helper III
Helper III

Slicer not slicing measures with filters

Hi All,

 

I have an issue where page slicers aren't slicing measures which contain filters in the DAX (using the same dimension field as the slicer...).

As an example, I have a Product Type dimension which contains a field called 'Product Sub-Type', with possible values 'A', 'B', 'C'. 

 

I also have a Product fact table containing various sales figures by major product, and sub-product. 

 

There's a measure that simply counts products for each product and sub-product:

Total Products = DISTINCTCOUNT('factProducts'[ProductID]')

There are then 3 specific measures which a filtered versions of the main measure for each sub-product:

Total Products for A = CALCULATE([Total Products], FILTER('dimProducts' , 'dimProducts'[Sub Product] = "A"))
Total Products for B = CALCULATE([Total Products], FILTER('dimProducts' , 'dimProducts'[Sub Product] = "B"))
Total Products for C = CALCULATE([Total Products], FILTER('dimProducts' , 'dimProducts'[Sub Product] = "C"))

 

I've then put the above into a matrix visual:

ProductTotal ProductsTotal Products for ATotal Products for BTotal Products for C
Apples1005020

10

Pears2004060100
Oranges30020100180

 

I've then added a slicer to the page for the sub-product dimension.

Using the slicer to slice for sub-product A results in the below behaviour:

ProductTotal ProductsTotal Products for ATotal Products for BTotal Products for C
Apples505020

10

Pears404060100
Oranges2020100180

*As you can see, it's sliced my main measure, but all the values still remain on the other measures in the same visual.

 

I would expect this to happen:

ProductTotal ProductsTotal Products for ATotal Products for BTotal Products for C
Apples50500

0

Pears504000
Oranges502000

 

Please can someone shed some light on why my filtered measures aren't working. I've done this many times before in the past and it's always worked, but i've not built a Power BI report for a little while so i'm wondering if MS have changed something in the interactions for measures.

 

Many thanks

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@Nickodemus 

The results I see in the file are fine, but not the same as what you showed earlier.

1. In the file, [Total Products for A] is defined differently from  how you defined it earlier:

Total Products for A =
CALCULATE([Total Products], 'dimProductSubType'[ProductSubType] ="A")

 With that, the result does not change regardles of the selection in the slicer. That is the normal behaviour for this code.

2. If we create a measure with the code you showed earlier, i.e.

Total Products for A V2 = 
CALCULATE([Total Products], FILTER('dimProductSubType', 'dimProductSubType'[ProductSubType] ="A"))

 the result is also as expected (but different from the result you talked about earlier). If the slicer selection is B or C, the measure will show blanks, which is what you claimed should happen. And it does

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

9 REPLIES 9
AlB
Super User
Super User

@Nickodemus 

The results I see in the file are fine, but not the same as what you showed earlier.

1. In the file, [Total Products for A] is defined differently from  how you defined it earlier:

Total Products for A =
CALCULATE([Total Products], 'dimProductSubType'[ProductSubType] ="A")

 With that, the result does not change regardles of the selection in the slicer. That is the normal behaviour for this code.

2. If we create a measure with the code you showed earlier, i.e.

Total Products for A V2 = 
CALCULATE([Total Products], FILTER('dimProductSubType', 'dimProductSubType'[ProductSubType] ="A"))

 the result is also as expected (but different from the result you talked about earlier). If the slicer selection is B or C, the measure will show blanks, which is what you claimed should happen. And it does

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

Ahhhh!!! I missed this because i was being lazy in PBI and used the quick measure which didn't include the filter function! Thanks for pointing out my mistake.

Nickodemus
Helper III
Helper III

@AlB - silly question, but i can't figure out how to attach my pbix to this post... Any tips please?

@Nickodemus 

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

AlB
Super User
Super User

@Nickodemus 

Perhaps you can share that mock, simplified pbix that reproduces the issue? With the info provided,  I pretty much agree with your reasoning. There must be something under the hood that we are not seeing in the example.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

Of course, let me knock a quick pbix together for the example. I'll post up here shortly.

Nickodemus
Helper III
Helper III

Hi @AlB ,

 

Not easily as it's a production dataset for my organisation - I've changed the names of the products and fields in this question, but the dax and interactions are exactly as in the pbix.

 

Is there anything obvious that would cause the issue i'm seeing, or is it behaving how you would expect?

AlB
Super User
Super User

Hi @Nickodemus 

Can you share the pbix?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Cheers 

 

SU18_powerbi_badge

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors