Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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
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
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.
@AlB - silly question, but i can't figure out how to attach my pbix to this post... Any tips please?
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 Please let me know if this works.
https://www.dropbox.com/s/mavlpyzac48rue2/ProductSubTypeTest.pbix?dl=0
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
Of course, let me knock a quick pbix together for the example. I'll post up here shortly.
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?
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
98 | |
39 | |
30 |