Slicer not slicing measures with filters

[Nickodemus]

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:

Product	Total Products	Total Products for A	Total Products for B	Total Products for C
Apples	100	50	20	10
Pears	200	40	60	100
Oranges	300	20	100	180

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:

Product	Total Products	Total Products for A	Total Products for B	Total Products for C
Apples	50	50	20	10
Pears	40	40	60	100
Oranges	20	20	100	180

*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:

Product	Total Products	Total Products for A	Total Products for B	Total Products for C
Apples	50	50	0	0
Pears	50	40	0	0
Oranges	50	20	0	0

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

[AlB · Accepted solution]

@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 

[Nickodemus]

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]

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

[AlB]

@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).

[Nickodemus]

@AlB Please let me know if this works.

https://www.dropbox.com/s/mavlpyzac48rue2/ProductSubTypeTest.pbix?dl=0

[AlB]

@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 

[Nickodemus]

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

[Nickodemus]

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]

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