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 =
    SWITCH(
        TRUE(),
            MeasureName = "Num Sales",1,
            MeasureName = "Num Orders",1,
            0 
        )

VAR CurrencyFormatDecimal =
    SWITCH (
        TRUE (),
            MeasureName = "Sales", 
                SELECTEDVALUE ( 'Reporting Currency'[FormatString] ,
                    """$"" #,0;-""$"" #,0;""$"" #,0"
                    ),
           SELECTEDMEASUREFORMATSTRING()
              )
                  
VAR FinalIf =
    IF (
        SkipConversion,
        SELECTEDMEASUREFORMATSTRING (),
        CurrencyFormatDecimal 
            )
RETURN
  FinalIf
    • 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.

Summary

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!