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
Ceeeg
Frequent Visitor

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 simple example that reproduces the issue. Google Drive link to example .pbix

 

My model is arranged as a Snowflake schema, with a fact table (Sales), linked to a dimension table (Stores), which is in turn linked to another dimesion table (Store Type Reference), alongside a measure table to store all my measures:

Model View Annotated.PNG

I'm trying to create a table visual that shows the sum of 'Value' in the Sales table for each 'Store Name' in the Stores table, showing a 0 if there is no data in the fact table for the store. I want to be able to filter this visual using slicers and filters based on the Store Type Reference table.

 

If I use the formula below, the filtering behaves as I would expect, but if a store doesn't have data in the Sales table (e.g. Store B), it doesn't appear (or, if I enable 'Show items with no data', it shows up but has a blank cell in the Sales column).

 

Sales = 
SUM('Sales'[Value])

 

However, if I add a +0 to the end of that formula, stores that have no data in the fact table do show up with a 0 in the Sales column, but they remain visible in the table no regardless of what filters I apply based on the Store Type Reference Table (the calculation itself is filtered correctly, so they show up as a 0). I've checked to see that 'Show items with no data' is not enabled. Filtering directly on the Store table does work as expected. 

 

Sales Plus Zero =
SUM ( 'Sales'[Value] ) + 0

 

It is as though adding the +0 to the measure somehow disables the propagation of the relationship from the Store Type Reference table to the Stores table, causing all rows to be displayed, but at the same time the relationship does propagate all the way down to the fact table for the purposes of the calculation?

 

I've found a couple of older posts that touch on this issue:

 

Greatly appreciate any insight you can provide - this is doing my head in!

 

Cheers,

Calum

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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 YouTube.gif 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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@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 YouTube.gif 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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ceeeg
Frequent Visitor

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.

 

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

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

defrugo
Frequent Visitor

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

Ceeeg
Frequent Visitor

Hi @defrugo, while it's still not really intuitive behaviour, @parry2k's explanation and solution are basically it - if you can manage to change your model from a snowflake schema to a star schema, it's going to fix the problem, and make things easier in general.

 

I recently did SQLBI's 'Mastering DAX' course, and I think after that, if I put my mind to it and had enough time, I might be able to fully understand why it doesn't work like we expect, and a workaround measure that does work. But if that's all you're after, it'd be a bit of an expensive answer for the question haha (the course is good if you're interested more generally though).

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.