Stephane Viot on 07 Oct 2015 23:52:58
Count of should return 0 instead of '(Blank)' (looks better on a Dashboard)
Administrator
You can do this with the COALESCE function: https://docs.microsoft.com/en-us/dax/coalesce-function-dax. For example:
= COALESCE(SUM(FactInternetSales[SalesAmount]), 0)
- Comments (54)
RE: Count should return 0 instead of (Blank)
Agree with Chris. We're trying to give a toggle box in Card options to show a 0 instead of blank - what if we don't want to have to band-aid this with a measure? I've got filters on the card instead.
RE: Count should return 0 instead of (Blank)
Unfortunately, you're very short-sighted, perhaps resulting from too much developer and not enough business insight. While coalesce may work, unless I'm mistaken, it doesn't work when the dataset returns all zeros. When used for drill-through purposes, BLANK still returns when coalesce is used...unless I'm using it incorrectly. More attention should be paid to the numerous user requests rather than developer opinion. We know our businesses and use cases better, theoretically anyway.
RE: Count should return 0 instead of (Blank)
Since it happens so frequently with the Card visual, this would be nice as an option, rather than polluting the model with a measure just to show 0 instead of blank.
RE: Count should return 0 instead of (Blank)
Count functions (all of them ) should return 0 (zero) value instead of in current use cases where is shown if there are no rows containing counted items.
I consider this as a bug since all other numbers are shown ok in count functions.. except 0 (zero).
RE: Count should return 0 instead of (Blank)
Instead of "fixing it" to 0, make this as some sort of settings, how to treat blanks in specific visuals. For example, in a table in some cases (blank) (or well, empty cell) is fine, in card visuals however...usually 0 makes more sense.
RE: Count should return 0 instead of (Blank)
Power BI team needs to expand this and also address other visuals which return a blank visual when there is no data. It is very confusing for end user who sees a blank visual (just because the filters selected have no data to return). Its been 5 years since this idea has begun still not been considered :(
RE: Count should return 0 instead of (Blank)
How do you display 0 instead of blank with count(distinct) with defined filter? The filter is the one that displays BLANK, not the actual count.
RE: Count should return 0 instead of (Blank)
0 is a valid number not a null entry and so should be displayed as such when using an operator such as count, sum etc.
RE: Count should return 0 instead of (Blank)
this needs to be combined with https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16587064-add-checking-on-blank-for-cadr-visual
RE: Count should return 0 instead of (Blank)
bandaid solution - create measure with simple count formula and add "+0" at the end.
Add same column to card visual and perform 'Count' or 'Count(distinct)' from GUI - ends up "(blank)" when it tries to count empty set ...
bandaid solution is minor inconvenience with 1 or 2 card visuals in report ... now use that with 10 or 20 visuals on single report ...
worst case scenario - you have measure 'custom count' for every single column in dataset ...
please add "display 0 instead of (blank)" to "Count" and "Count (Distinct)" options in GUI on every visual supporting this type of calculation