SUM Measure Returns Incorrect Results With IF Statement

[Anonymous]

Hello,

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

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

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

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?

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

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

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.

[Ashish_Mathur]

Hi,

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

[Phil_Seamark]

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?

[Anonymous]

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?