Formatting (Blank) in Power BI using a Calculation Group

Colin Maitland, 04 August 2021

In this article, I will demonstrate a simple method to format ‘(Blank)’ on a Data Card in a Power BI report as ‘0’ or ‘-’ rather than ‘(Blank)’.

This image shows a page from a report in Power BI. Notice that the values displayed by the Data Cards for Open Revenue and Due to Close Revenue near the top right-hand corner of the page display ‘(Blank)’.

Notice here that these ‘(Blank)’ values are now displayed as ‘$0’.

Notice here that these ‘(Blank)’ values are now displayed as ‘-’.

These changes have been applied to the Data Cards using a Calculation Group with the following Calculation Items. I use Calculation Items such as these to format values returned by Measures. I have named this Calculation Group ‘Format’ and renamed the column for the Calculation Items from ‘Name’ to ‘Format’.

The ‘Blank’ to Zero’ Calculation Item transforms blank values returned by the selected measure to 0:

The ‘Blank’ to Dash’ Calculation Item transforms blank values returned by the selected measure to a dash:

These Calculation Items may be applied as filters to Measures, Slicers and Visualisations.

To apply the ‘Blank to Zero’ Calculation Item to the Measures displayed by the Data Cards in my report, I simply filter each Data Card by the Calculation Item named ‘Blank to Zero’.

To apply the ‘Blank to Dash’ Calculation Item to the Measures displayed by the Data Cards in my report, I simply filter each Data Card by the Calculation Item named ‘Blank to Dash’.

Finally, Calculation Groups and their associated Calculation Items cannot be added or edited using Power BI Desktop. However, they can be by using Tabular Editor. You can download the free version of Tabular Editor from here and the paid version from here.