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)’.

image

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

image

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

image

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’.

clip_image007

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

clip_image008

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

clip_image009

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’.

clip_image011

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’.

clip_image013

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.

// Tactix campaign Linkedin Tag_April 2022