Strange behaviour when I want to show blank as 0

[rpinxt]

I have this visual :

As you see the IFU InvQ is blank. It is a simple sum formula.

So I wanted it to show as 0 as follows :

Whats going on here?

Why do I get hundreds of lines showing 0?

Has it to do with the tables being connected as many to many?

[Greg_Deckler · Accepted solution]

@rpinxt Try this:

Test = IF( ISBLANK( [DelQ] ), BLANK(), SUM('IFU Inventory'[Total Qty]) + 0 )

When you return a 0 then every combination will return that 0 which means that everything shows up in the matrix or visual.

[rpinxt]

Thanks @Greg_Deckler !

This indeed seems to do the trick:

Seems to be working in all situation where IFU InvQ is filled, blank or zero.

Bit puzzeled as to why DelQ is involved as this should not be empty anyway.

But do understand what you say about when it brings in a zero it will bring in a zero for al combinations and then the visual will also show all the combinations.

[Greg_Deckler]

@rpinxt Right, you have the concept. The specifics of how to "fix" it will vary depending on the situation, I was just trying to puzzle out a potential fix given the information you provided, the real fix may vary depending on your data and use case.