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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JWA___
Regular Visitor

Overriding slicers

I have a very simple model:

1 dim table:

My_dim
dim1
dim2
dim3
dim15

 

1 fact table:

DateMy_dimNr
1-1-2022dim15
2-1-2022dim220
1-1-2022dim350
2-1-2022dim1100

 

I create a simple measure:

Measure = SUM('Fact Table'[Nr])
 
On top of this I want to create a help table like this:
Help table = ALL('Dim Table'[My_Dim])

In the data model I link both my Dim Table as wel as my Help table to my Fact table (via the My_dim column). Both a one to many relationship, with the fact table on the many side.
 
Now I put a table on my canvas with my measure against my Help table[My_Dim].
Next to it I put a slicer with the column 'Dim Table'[My_Dim]. Let's say I select the value "dim1".

When I select one value in this slicer I expect the table to be not responding... however the table filters to dim1.... 
Why????
 
(I'm trying to replicate something on the overriding slicers side, but I'm completely puzzled why my Help table seems to be filtered by the slicer selection of my Dim Table.....)
1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @JWA___ ,

 

When you select a value (e.g. "dim 1"), the measure will only return 105 for the "dim 1" row, and the other rows will be blank. At this point Power BI will hide the rows with blank values by default. You can check "Show items with no data" to show the hidden rows. 

 

vkkfmsft_0-1657006573671.png

vkkfmsft_1-1657006705962.png

 

You can also change the measure to the following formula.

 

Measure 2 = CALCULATE(SUM('Fact Table'[Nr]), ALL('Dim Table'))

vkkfmsft_2-1657006830430.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
truptis
Community Champion
Community Champion

Hi @JWA___ ,

Try to stop interactions with the slicer and the table. For that you can go to "Edit Interacions" option under "Format" at the top ribbon.

 

If this helps you, then please hit the thumbs up & mark it as a solution. Thanks.

v-kkf-msft
Community Support
Community Support

Hi @JWA___ ,

 

When you select a value (e.g. "dim 1"), the measure will only return 105 for the "dim 1" row, and the other rows will be blank. At this point Power BI will hide the rows with blank values by default. You can check "Show items with no data" to show the hidden rows. 

 

vkkfmsft_0-1657006573671.png

vkkfmsft_1-1657006705962.png

 

You can also change the measure to the following formula.

 

Measure 2 = CALCULATE(SUM('Fact Table'[Nr]), ALL('Dim Table'))

vkkfmsft_2-1657006830430.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thanks, this does make sense. I thought of this last weekend, but this articulates it clearly, thank you.

PaulDBrown
Community Champion
Community Champion

Are both relationships one-to-many SINGLE direction from the dim table to the fact table?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I have the following relationship

 

Dim 1 : n Fact (single)
Dummy Dim 1 : n Fact (single)

 

I thought filtering on my Dim would never impact the records of my Dummy dim, because the filtering goes from Dummy Dim to Fact and not the other way around.
However I guess the Dim table does filter the fact table, to just one or a few records. And I suppose that Power BI only shows the Dummy Dim records for which there is a fact....

That is not what I expected after reading the article of @Phil_Seamark : https://dax.tips/2020/02/05/overriding-slicers/

Somehow filtering on his original date table, doesn't affect anything on the dates of his dummy table.

I just want to understand how this works and replicate the gist of the article.

So this is NOT about interactions. 🙂

Yes

Nathaniel_C
Super User
Super User

Hi @JWA___ ,

Not sure if I follow what you are trying to do, however to override a slicer, you can use the Format/ Edit Interactions as I have done below.  The table on the right is turned off, so it shows all, and the card on the left is left on, so it only shows the selected value.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 

Nathaniel_C_0-1656705251125.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This is not what I want to achieve, 
I want to do something similar als described in this blog post. But if I do what I described here for my dim table instead of the date table in the article, it doesn't work.....
https://dax.tips/2020/02/05/overriding-slicers/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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