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)
Creating a measure for every card is too excessive and limits functionality......
RE: Count should return 0 instead of (Blank)
Adding zero to the measure does not work well since it breaks formatting on other visuals such as charts.
RE: Count should return 0 instead of (Blank)
Also note ... The best way of handling nulls is by using a find replace in Power Query.
Where ever possible data transformations should be performed in Power Query.
RE: Count should return 0 instead of (Blank)
Important as alternative would be to use IsBlank which would mean performing the COUNT twice.
RE: Count should return 0 instead of (Blank)
Adding a 0 is fine, but it's not always a solution. Not in all cases. I agree with David Cadman, an option to choose how to display (Blank). Sometimes 0 is alright, something plain text saying "None". Anything but (Blank).
RE: Count should return 0 instead of (Blank)
Adding + 0 to your formula should take care of the blank
RE: Count should return 0 instead of (Blank)
It makes sense that Blank is returned by formulas sometimes, but on say the Card visual it would make sense to have the option "Display (Blank) as 0" Y/N
RE: Count should return 0 instead of (Blank)
DAX returning blank makes sense because of what it means in the context of charts. If there is no data for a particular dimension, then generally we do not want it to draw a line or a bar. However, in the case of cards, I believe the default behavior would be best if it showed zero instead of (Blank). Given that (Blank) is the established behavior, I would ask that a formatting option be created for card and multi-card to show 0 instead of blank.
RE: Count should return 0 instead of (Blank)
Adding a zero on the measure worked, great workaround.
RE: Count should return 0 instead of (Blank)
Quick work around if anyone is interested. Adding a zero forces Power BI to display Zero!
CALCULATE(COUNTROWS(table),table[column]) + 0