PowerBI empty values row not displayed

user_6400457 0
Viewed 956

I have a confusing mystery...

Simple DIVIDE formula works correctly. However blank rows are not displayed. I attempted a different method using IF, and now the blank row is correctly displayed. However this line is only displayed if I include the IF formula (which gives a zero value I don't want).

Formula 1:

Completion % = DIVIDE(SUM(Courses[Completed]),SUM(Courses[Attended]),BLANK())

Formula 2:

Completion % with IF = IF(SUM(Courses[Attended])=0,0,DIVIDE(SUM(Courses[Completed]),SUM(Courses[Attended])))

With only the DIVIDE formula:

using only DIVIDE

Including the IF formula:

enter image description here

It appears that Power BI is capable of showing this row without error, but only if I inlude the additional IF formula. I'm guessing it's because there is now a value (0) to display.

However I want to be able show all courses, including those that have no values, without the inaccurate zero value.

I don't understand why the table doesn't include these lines. Can anyone explain/help?

dax powerbi
2 Answers

The point is very simple, by default Power BI shows only elements for which there is at least one non-blank measure.

The DIVIDE operator under-the-hood execute the following:

IF(ISBLANK(B), BLANK(), A / B))

You can change its behaviour by defining the optimal parameter in order to show 0 instead of BLANK:

DIVIDE(A, B, 0) will be translated in the following:

IF(ISBLANK(B), 0, A/B))

Proposed solution

Those mentioned avobe might all be possible solutions to your problem, however, my personal suggestion is to simply enable the option "show item with no data" in your visualization.

user_86812290

While DIVIDE(A, B, 0) will return zero when when B is zero or blank, I think a blank A will still return a blank.

One possibility is to simply append +0 (or prepend 0+) to your measure so that it always returns a numeric value.

DIVIDE ( SUM ( Courses[Completed] ), SUM ( Courses[Attended] ) ) + 0
user_7652260
Related