Donut Chart Blank Question

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I’ve created a donut chart and for some reason it shows a blank value in the state name.

I’ve gone through my measures and table relationships and can’t seem to figure out why the blank is being displayed.

image

All Adviser Lodged FUM = 
CALCULATE (
    SUM ( 'Access DB Lodgements'[Annual Premium/FUM] ),
    FILTER (
        'Access Active Advisers',
        'Access Active Advisers'[Access Level] <> ""
    ),
    FILTER ( 'Access DB Lodgements', 'Access DB Lodgements'[Type] = "Investment" )
)
    + 0

Adviser Selected Lodged FUM (All) = 
CALCULATE (
    [All Adviser Lodged FUM],
    FILTER ( 'Access DB Lodgements', 'Access DB Lodgements'[Excluded FUM] = "No" )
)
+0

image

Thanks in Advance

2

There is likely additional values in your fact table that aren’t in your lookup table.

Is the blank value evaluating to anything?

Quickly place it into a table and it should tell you.

Also see what happens if you use the fact dimension versus the lookup dimension. See if there is a difference to the results. Theoretically they should be the same but they likely aren’t due to this problem you’re seeing

3

Hi Sam,

i’ve had a look and this is what i found:

Fact Table:

image

Lookup table:

image

lookup table values:

image

The reason i want to use the lookup table is because I’ve grouped the states through the relationship.

Still trying to understand why the blank would come up if they are linked. Ideally it would need to be classed under other. I’ve checked through the data in the fact table and cant see any blanks/or states that are not listed.

4

Yes you definitely want to use the lookup table.

All I can really say without testing it myself is that there will be some value somewhere that is causing the blank.

Another way to check it is to recreate a few columns from the fact table in your report page.

Then click on the blank line to then filter that data. This should be a quick way to see what row in the fact table are being accumulated there

5

Hi Sam,

Thank you for your comments.

I followed through your suggestions and nothing came up in the search.

So I went through each of the DAX measures and 4 hours later found something interesting.

If i removed the +0 (At the end of each measure) the blank did not display.

So i guess my question now is why would a +0 at the end of the measure be the cause of this issue?

Thanks

6

Interesting, I didn’t notice the zero. Makes sense though.

Even if there is a hard coded zero then Power BI will registered a result and in this case there wasn’t a corresponding lookup value so it went to blank.

If you just leave it to evaluate to blank then that should work fine as you’ve discovered.

7

Thanks for the feedback Sam!!