In the last Power BI tips and tricks article, I wrote about the importance of learning DAX to open up the full capabilities in Power BI. In this article, I’d like expand on that and offer a few resources that I have found particularly helpful as I’ve learned and worked with DAX over the last 7+ years. I trust you’ll find these useful as well.
DAX Studio
DAX Studio is a free tool, available for download at the URL above, that is primarily based around DAX queries. Utilizing DAX for queries is not often necessary in working with Power BI, but we can still utilize the features of DAX Studio by using DAX Statements to slightly alter the DAX written for measures, columns, or tables in Power BI to satisfy the syntax requirements of DAX queries that you can then execute in DAX Studio.
DAX Studio feels to me like the DAX equivalent of using SQL Server Management Studio (SSMS) for SQL. I primarily like to use DAX Studio for performance tuning and testing on long, complex DAX measures. I find that being able to break the measure down into pieces and “seeing” the results of each piece helps me to troubleshoot faster and make measures more performant more quickly than if I just worked in Power BI alone. DAX Studio also has DAX Formatter built in, more on DAX Formatter below.
DAX Studio integrates very well with Power BI by allowing you to connect directly to your Power BI model and when installed, is also available as an External Tool within Power BI.
Tabular Editor
Tabular Editor began as a aptly named free tool to work with Tabular modelling in Power BI and SQL Server Analysis Services (SSAS). Version 1 and version 2 are still available as free downloads at the URL above, and Tabular Editor 3 (the newest version at the time of this writing) is available for purchase with additional features. I currently use version 2 and find that it meets my needs.
Tabular Editor is a capable but pretty lightweight tool that I have used in a variety of scenarios both Power BI and SSAS. On the Power BI side, I find really handy for making changes to metadata within my models such as changing metric or column formatting, adding descriptions, formatting DAX using DAX Formatter, plus more. Tabular Editor is more efficient at tasks like this, especially when there are large amounts of changes being made. Tabular Editor also makes it easy to perform these changes in bulk, something that can’t really be done in Power BI. Plus, if you’re using or looking to implement calculation groups, Tabular Editor works well there and tends to be a favorite in the community for this.
Tabular Editor also integrates well with Power BI by allowing you to connect directly to your Power BI model and when installed, is also available as an External Tool within Power BI.
DAX Formatter
Another aptly named tool, DAX Formatter takes any DAX expression and formats it for easy readability. You may not need to use this on simple base measures (though once I get started formatting my measures I tend to do everything!), but this is especially useful for long measures. DAX Formatter is available as a web app at the URL above or is baked into both DAX Studio and Tabular Editor.
DAX Patterns
DAX Patterns is a website (and book) from the folks at SQLBI (remember the Italians, Marco Russo and Alberto Ferrari from the last post? SQLBI is their company). I still find this site immensely helpful and learn new things reading this website’s articles!. The site contains several patterns, reusable DAX code, for frequent calculations such as time intelligence, ranking, comparing data of different granularities, cumulative totals, plus others. Each pattern comes with thorough explanations of how the code works and how to implement it in different scenarios. Some of these calculations can get quite complex so it’s beneficial to have access to performant code that needs little to moderate tweaking to implement. And best of all, it’s free to get started with these patterns! I highly recommend this site as you get more comfortable with DAX and look to expand your knowledge of the language.
In Conclusion
There are certainly dozens of other sites, blogs, and resources that you can utilize to become a DAX ninja, but these are a few of my favorites. What do you recommend? Feel free to leave a comment below.
The next article will wrap up the DAX section of Power BI tips and tricks (at least for now), and we’ll look at a few ways that you can keep your DAX organized.
