Quick tip on how to ensure your Format String Expression in the Power BI Calculation Group works
I was recently working on a more complex calculation group item and the corresponding Format String Expression.
One of the things I did learn is that the Format String Expression is actually like any other DAX expression. It works in exactly the same way! How cool is that.
Currently the only way to create and or edit the Format String Expression is in Tabular Editor. Whilst I love Tabular Editor it does not have any DAX Intellisense.
I kept on editing the Format String Expression and I honestly must have had to edit it 4 times and it still was wrong.
Below is a quick tip on what I did to make sure my Format String Expression was valid and would indeed work!
- I went into Power BI Desktop.
I then clicked on New Measure
- I left the default name of “Measure =”
- I then pasted in my Format String Expression
- If you notice above in line 10 there is an issue
- I then clicked on the tick button for my measure
- I then got the following error as shown below
This allowed me to and correct the DAX measure for the Format Property String.
- Not only that the DAX syntax above looks terrible.
- I went to the amazing DAX Formatter and formatted the measure
- I then pasted this back into my measure to make sure it is working as expected.
- Now that looks better!
I now went back to Tabular Editor and pasted the code into the Format String Expression
- NOTE: I excluded the measure name “Measure =”
- I then saved in Tabular Editor and I could now use my calculation group in my report.
In this quick tip blog post I have shown a way to easily make sure that not only the Format String Expression is valid, but that it is also easy to read.
Thanks for reading, I hope it is a tip that you can use in the future.
Comments and any suggestions are always welcome.