Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.