Forum Discussion
SUM Measure Returns Incorrect Results With IF Statement
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 acIt'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
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?
- 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?
Hi,
What problem do you face in using the formula without the IF statement?
