DAX Formatter tool for Power BI Desktop

Posted on

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 Pattison 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 8 votes
Article Rating

Leave a Reply

10 Comments
Inline Feedbacks
View all comments
Didier Terrien
Didier Terrien
2 years 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

0
Reply
Phil Seamark
Author
Phil Seamark
2 years ago
Reply to  Didier Terrien

HI Didier, Thanks for the kind feedback. You are correct that only measures are currently supported, so I’d recommend you take a backup copy before using this on a model destined for production. The changes being made under the cover aren’t huge (updating the text in an expression in a mode), so is probably low risk and probably fine. But taking a backup before starting would be a good idea. In terms of overloading the service. I spoke with the SQLBI folks and they are already in the process of updating the service point to cope with far more that… Read more »

1
Reply
Görkem
Görkem
2 years 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 ?

0
Reply
Phil Seamark
Author
Phil Seamark
2 years ago
Reply to  Görkem

Is your model in Power BI Desktop using Import, Direct Query or Live Connect? The first two should work but probably not Live Connect

0
Reply
Görkem
Görkem
2 years ago
Reply to  Phil Seamark

Yes Power BI Desktop using Import Data

0
Reply
Thibault
Thibault
2 years 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 »

1
Reply
Phil Seamark
Author
Phil Seamark
2 years ago
Reply to  Thibault

I had the wrong client library in my link. Please use the following

Microsoft.AnalysisServices.NetCore.retail.amd64

not the Microsoft.AnalysisServices.retail.amd64

you may need to run

dotnet remove package Microsoft.AnalysisServices.retail.amd64

then reinstall

0
Reply
Andy Parkerson
Andy Parkerson
2 years ago

Just came here after Patrick from Guy in a Cube showed off your work on their Saturday morning livestream. Followed the directions and how have a wonderful formatter button.

This idea of programmatically changing PowerBI files is making me thing of many different uses for this, such as auto-generated documentation by pulling the descriptions off of the measures, or saving all of the DAX and M in a formatted text file for source control.

Thanks so much to you and Ted Patterson for this!

1
Reply
Didier Terrien
Didier Terrien
2 years ago

@Andy Parkerson

Saving all of the DAX and M + the layout in a formatted text files for source control is available in Power BI Sidetools
https://thebipower.fr/index.php/power-bi-sidetools/

0
Reply
David
David
2 years ago

Hi Phil, thank you for sharing this work. I was looking for a way to efficiently create a data dictionary for an existing model, and between using DMVs against the model and your DAX formatter tool here, I can get a nicely formatted list of all measures and calc columns in a few clicks.

0
Reply