Recently I was working on a customer’s data and one thing that was missed was when something was not selected the Calculation Group did not return any default Format String.

Below is how I solved this, with the tricky part being formatting of the measure to be displayed correctly.

This follows on from my previous blog posts with regards to Calculation Groups:

Power BI Visuals now support Custom Formatting for measures using Calculation Groups!

Create Currency Formatting Strings using Calculation Groups in Power BI Pro & Premium / Azure Analysis Services / SQL Server Analysis Services 2019

Before the change was made this is what the Calculation Group would look like without having a Format String for my [Sales] measure

In order to get the default format string, I did the following.

  • Using Tabular Editor 3, I went into my Calculation Group called “Currency Symbol” and then went to ‘Metric Currency’
  • In the Properties Window I then click on the down button next to Format String Expression
  • I then copied the Format String Expression into DAX studio for easier editing.
    • VAR MeasureName =
          SELECTEDVALUE ( 'Metric'[Metric], SELECTEDMEASURENAME () )  
       VAR SkipConversion =
                  MeasureName = "Num Sales",1,
                  MeasureName = "Num Orders",1,
      VAR CurrencyFormatDecimal =
          SWITCH (
              TRUE (),
                  MeasureName = "Sales", 
                      SELECTEDVALUE ( 'Reporting Currency'[FormatString] ,
                          """$"" #,0;-""$"" #,0;""$"" #,0"
      VAR FinalIf =
          IF (
    • The important part to pay attention to here is in line 17, where I have put in the default format string value for my [Sales] measure.
      • “””$”” #,0;-“”$”” #,0;””$”” #,0″
    • I had to prefix it with the double quotes and then also escape it so that it would be correctly identified when the DAX compiler was run.
    • The result of the above syntax is that it would look like the following.
      • “$” #,0;-“$” #,0;”$” #,0

And this is what the result now looks like when nothing is selected for my [Sales] measure.


It is always important to make sure that when creating a Power BI report, that it is easy for the users to read and explore their data. Whilst this is a small fix, it is often critical when working with different currencies. It saves the users a lot of time trying to figure out if it is in the base currency or an alternate currency.

Thanks for reading, questions and comments are most welcome!