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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SUM Measure Returns Incorrect Results With IF Statement

Hello,

I'm stumped understanding what Power BI is doing.  I have a simple model:

PhaseTableRelationship.png

I placed a filter on the dimension table Jobs to filter to a specific job:

JobTable.png
I created a simple table with 4 columns from the Job Details table (Fact Table):

PhaseTable.png
This is as expected.  Then things go sideways when I create a simple measure:

Actual Cost = VAR ac = SUM('Job Details'[ActualCost]) RETURN IF(ac = 0, 0, ac)

Suddenly a bunch of non-related data is pulled in, what's going on?

PhaseTableWrong.png

If I drop the IF statement in the measure, it works as expected:

Actual Cost = VAR ac = SUM('Job Details'[ActualCost]) RETURN ac

PhaseTableRight.png

It's clearly the IF in the measure driving this behavior, but why? Why is it pulling in non-related rows? Any insight is appreciated.

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

What problem do you face in using the formula without the IF statement?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Phil_Seamark
Employee
Employee

Hi @Anonymous 

 

This is because you are forcing the calculation to return a value for all permutations of the dimentions, even if they have no data.  Why are you trying to return a zero in this case?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

This was my attempt to return 0 in blank scenarios. I guess I'm not understanding why it isn't just summing with the context filters in place (job, phase, cost type). I wasn't aware it would do this across all permutations outside of the current context. How does one show 0 for values that are blank since the visual won't show blanks?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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