DAX Formatter tool for Power BI Desktop

Last week I was honoured to take part in the latest edition of the Power BI Dev Camp which is run by my colleague Ted Patterson. It was a fun session which I enjoyed.

As part of the Dev camp, I walked through some of my recent Visual Studio Code based blog posts on how to perform various tasks against models hosted in Power BI desktop.

While preparing for the session, Ted and I agreed that it might be helpful to create a small external tool that could automatically format all DAX expressions in a Power BI model. The idea is to leverage the excellent DAX Formatter API provided by the good folks at SQLBI. This API is the same endpoint used when you format your DAX using DAX Studio.

Build your own using Visual Studio Code

This blog shows how you can create your own DAX Formatting tool from scratch using Visual Studio Code. You can then add the tool to the External Tools button on the Power BI Ribbon so that all your DAX expressions can be beautified at the single press of a button.

HASHCODE

I’m conscious that this is a free endpoint, so wanted to make sure any helper tool does not overload the API unnecessarily, so designed the script in a way that reduces the number of times the API gets called.

The idea is to store a HASHCODE value that represents the DAX Expression in an Annotation. An Annotation in Analysis Services is a place you can store items of text that aren’t directly involved in the calculation but can be useful for other reasons – often to help with documentation.

If a DAX expression, such as a measure, calculated column, or calculated table does not have an annotation, we send the text to the API and receive back some formatted DAX. We create a HASH value of the formatted text and store this in the annotation.

The next time the script runs, we create the HASH value from the annotation and compare it with the HASH value over the current expression. If the two values match, the DAX expression is still formatted, and there is no need to send it off to the API again.

The Script

To get this up and running, you need to complete the following steps.

  • Install .Net Code SDK (version 3.1 or version 5.0)
  • Install the latest copy of Visual Studio Code
  • Create a blank project folder and open in Visual Studio Code
  • Create project files by running following command in the terminal window
    • dotnet new console
  • Install the required packages
  • Copy ALL code from the Program.cs file from the asset folder into your copy of the program.cs file
  • Run the script against a PBIX file open in Power BI Desktop
    • Be sure you have updated the model to V3
    • Update the port number at line 16 for testing (not required if launched as External Tool)
  • Copy the External Tool JSON file from the asset folder to the External Tools folder on your machine.
    • Open, update and edit the path property to point to where ever your app is created.

The Video

Summary

All the steps required to get this up and running are the same as my recent blog posts. The only different thing is the code pasted into the Program.cs file – and the packages required to get installed to the project.

The main point of this blog is not to provide you with a long-lasting tool, instead to show you how you can solve interesting use cases with the Tabular Object Model (TOM) and Visual Studio Code.

Big thanks to Ted Patterson for helping me on this as well as SQLBI for providing an excellent API endpoint.

If you don’t want to mess with Visual Studio Code and just want the External tool, simply grab the net5.0.zip file from the Asset folder and extract to a folder on your Windows 10 machine. You’ll need the DAXFormatter.pbitool.json file as well (don’t forget to update the path property). You’ll possibly also need to install the .net 5.0 runtime if it doesn’t work.

If you need to tweak the code to support localization, adjust the JSON payload posted to the API endpoint at line 180.

Another tweak could be to connect to existing models in Azure Analysis Services, or to models hosted in Power BI Premium. All you need to do is update the connection string and run locally in Visual Studio Code.

5 5 votes
Article Rating

Leave a Reply

7 Comments
Inline Feedbacks
View all comments
Didier Terrien
Didier Terrien
1 day ago

Hello Phil,
Thank you. That’s a great post ! I have 2 questions :
– Only measures writing is officially supported at the moment in the desktop. Do you think there might be some side effects writing calculated columns and tables ?

– Each time a pbix file will be scanned for the first time, the service will receive potentially many requests. Do you think it is acceptable from the service point of view ?

The posts of this series are inspiring. Can’t wait for the next one.
Regards
Didier

Görkem
1 day ago

Hi Phil,
That’s amazing post. I am really impressed about idea and result.

I want to ask a question about running project.
I applied all steps as you mentioned. I completed all installation and other things. I checked the port number from both cmd and DaxStudio, It was same. I also changed console line to externalTerminal. However I’m getting error when I tried to run Program.cs. It refers the line 19 and says “Connection cannot be established. Make sure the server is running”

Do you have any idea about this ?

Görkem
1 day ago
Reply to  Phil Seamark

Yes Power BI Desktop using Import Data

Thibault
Thibault
1 day ago

Hi Phil, Thanks this looks like a handy thing to have in my toolkit. I followed the instructions but am getting the below error when running Exception has occurred: CLR/Microsoft.AnalysisServices.ConnectionException An unhandled exception of type ‘Microsoft.AnalysisServices.ConnectionException’ occurred in Microsoft.AnalysisServices.Core.dll: ‘A connection cannot be made. Ensure that the server is running.’ Inner exceptions found, see $exception in variables window for more details. Innermost exception System.IO.FileNotFoundException : Could not load file or assembly ‘System.Configuration.ConfigurationManager, Version=4.0.3.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’. The system cannot find the file specified. at Microsoft.AnalysisServices.Platform.Configuration.ConfigurationHelper.TryGetAppSettingsIntValue(ConfigEntry entry, String key, Predicate`1 validator, IDictionary`2 configuration) at Microsoft.AnalysisServices.Platform.Configuration.ConfigurationHelper.LoadConfiguration(IDictionary`2 configuration) at Microsoft.AnalysisServices.Platform.Configuration.ConfigurationHelper.EnsureConfigurationIsLoaded() at Microsoft.AnalysisServices.Platform.Configuration.ConfigurationHelper.get_Connectivity() at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(IConnectivityOwner… Read more »