Forum Discussion

Ceeeg's avatar
Ceeeg
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Measure Column in Table Visual Causes Filters to be Ignored

Hi, I'm encountering some unexpected behaviour when I add a measure as a column into one of my table visuals.   My actual file has confidential data, so I can't share it here, so this is just a sim...
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    Ceeeg  I can try to explain but I know why it is not working. This is why star schema is the recommended/best practice. To visualize the issue, in a table visual where you have store and sales with zero measure, add store type from the store type table, now filter on Fashion and you expect to see two stores (A and B) but you will see other stores as well showing for the selected type even though those stores are not associated with type fashion and the reason for that is we are forcing to add zero to the measure. So this is the main explanation why you see all the stores.

     

    To overcome the best practice is to use star schema and/or update your measure something like this: 

     

    Sales Plus Zero = 
VAR __ForceZero = IF ( NOT ISBLANK ( SELECTEDVALUE ( Stores[Store Name] ) ), 0 )
RETURN
Sum('Sales'[Value])  + __ForceZero

     

    I hope it makes sense. Great question though.

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

     

Ceeeg's avatar
Ceeeg
Icon for Advocate I rankAdvocate I
4 years ago

Update:

I created a new measure in the measure table:

store_name = 
MAX(Stores[Store Name])

I added this measure as a filter to the table visual that wasn't filtering as expected, and set it to 'is not blank'. After doing this, filtering on the Store Type Reference table seems to work as expected.

 

Not really sure why this works, and not a practical solution in my situaton (I need to use the Sales measure against multiple dimensions, so I'd have to make a measure for each dimension, and at that point I may as well just make a calculated column), but I thought it might be a useful clue for someone more knowledgeable than me.

 

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

    Hi  Ceeeg 

     

    I am so glad to hear that your problem has been solved . Please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Yadong Fang

    • Ceeeg's avatar
      Ceeeg
      Icon for Advocate I rankAdvocate I
      4 years ago

      Hi v-yadongf-msft 

      Unfortunately my update above isn't really a solution - at least not a practical one in my situaton - I need to use the Sales measure against multiple dimensions, and with this approach I'd have to make a measure for each dimension, so it'd be a fair bit of effort.

       

      Cheers,

      Calum

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Have you found solution for that? And the reason of that behaviour? I have the same issue. Why does it work like that?