cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Prometheus_2 Frequent Visitor
Frequent Visitor

DAX Measure SUM Behavior Question

Hello,

 

I'm looking to understand the following behavior:

 

I have the following meaures:

Actual Hours SUM = SUM('Job Details'[ActualHours])
Actual Hours With Zero = VAR AH = SUM('Job Details'[ActualHours]) RETURN IF(AH = 0, 0, AH)

 

When I place these measures with a Date in a table as well as a slicer on the page, I get different side by side results.   

2019-05-30 10_35_17-Untitled - Power BI Desktop.png

Why does the Actual Hours With Zero Measure ignore the Week Ending slicer? The grand total is correct in both cases, but the table with the Actujal Hours With Zero measure is showing rows that I thought would be filtered out by the slicer?  The purpose of Actual Hours With Zero is to show a 0 in situations where there is no total (blank). 

 

 

5 REPLIES 5
Super User
Super User

Re: DAX Measure SUM Behavior Question

Hi @Prometheus_2 

In the second case your measure always returns a value so it will be shown, no matter what (it does override the filter, by design). You need blanks for the visual not to show anything for a specific date.  

Prometheus_2 Frequent Visitor
Frequent Visitor

Re: DAX Measure SUM Behavior Question

Hmm, I thought the purpose of the slicer was to change the display context? Why would a measure override this?  Just looking to understand this.  How do you handle scenarios where you want to show 0 as a value instead of blank?  I can't filter the visual for zero values as it won't show 0 on days where it is 0.

 

What is the correct way to handle scenarios where you want to show a value as 0 instead of blank since a measure won't respect a filter.

 

In the previous screenshot, how would you show the value for a specific date (that's what I used the slicer for) when the value is blank but you want to show that as 0?

v-cherch-msft Super Contributor
Super Contributor

Re: DAX Measure SUM Behavior Question

Hi @Prometheus_2 

It seems you may check 'Edit interactions' for the slicer.If the two visuals are filtered by the slicer.The slicer would change the visual when you use no matter which measures.

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Prometheus_2 Frequent Visitor
Frequent Visitor

Re: DAX Measure SUM Behavior Question

Hi 

 

 

v-cherch-msft Super Contributor
Super Contributor

Re: DAX Measure SUM Behavior Question

Hi @Prometheus_2 

It depends on the relationships.I would suggest you use WeekEnding Slicer table's week ending column in the table.

Reference:https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 407 members 4,054 guests
Please welcome our newest community members: