A very common issue I see a lot of Power BI users struggle with is a visual (usually Card or KPI) returning (BLANK) instead of 0. There is no setting in the visuals to fix this. But we can easily fix this issue with DAX!
In this example, we have sales data for multiple years, but we had no sales in 2010. We create a card visual that shows sales and we add a slicer for year. Now we that we slice for 2010, we expect to see $0, but instead we see (BLANK).
We can fix this by creating a measure to replace sales for the visuals we have issue with.
The easiest way in DAX to fix this issue is to use an IF statement. The IF statement simply replaces the blank value with what we choose it to replace with. In this case, we want it to be 0. Here is the syntax :
* Please note that using a variable is optional. We could also write it like this: IF(SUM(FactInternetSales[SalesAmount]) = BLANK(), 0, SUM(FactInternetSales[SalesAmount]))
Don't forget to format your measure once created.
We can also replace the blank value with text such as "No Sales" or "N/A"
However, its important to know that replacing blank with 0, is not always preferable. For instance, if you have a matrix, you might want to show blank to know that there is no value. Or perhaps you have future dates in your date table but no sales yet.
If you don't need the original field then you can choose to hide it.
Comments