Setting the Default Format String with Calculation Groups in Power BI
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.
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!
[…] Gilbert Quevauvilliers figures out how to display a calculation group’s default format string …: […]
[…] Quevalliers, writing on Four Moo, noted that he was recently working with customer data and discovered that a Calculation Group […]