cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

 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

1 ACCEPTED SOLUTION
Super User

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

9 REPLIES 9
Super User

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

Helper III

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.

Helper III

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

Super User

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

Helper III
Super User

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

Helper III

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

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?

Super User

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors