Forum Discussion

JWA___'s avatar
JWA___
Regular Visitor
4 years ago
Solved

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.....)
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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. 

     

     

    You can also change the measure to the following formula.

     

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

     

    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.

     

8 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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. 

     

     

    You can also change the measure to the following formula.

     

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

     

    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.

     

    • JWA___'s avatar
      JWA___
      Regular Visitor
      4 years ago

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

  • truptis's avatar
    truptis
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

     

     

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • JWA___'s avatar
      JWA___
      Regular Visitor
      4 years ago

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