Analytics

Introduction to the DAX Studio

Two programmers working on some coding together

In this blog, we’ll go through a quick overview of all the options available in the user interface of DAX Studio. These include the panes and each button in the ribbon. We’ll discuss how using these features will help improve your performance in DAX Studio.

Familiarizing yourself with the purpose and function of each feature in DAX Studio is important. So, we’ll go through this from top to bottom.

Using The DAX Studio Ribbon

DAX Studio has three tabs on its Ribbon: HomeAdvanced, and Help.

Snip

1. The Home Tab:

The options under the Home Tab are grouped according to purpose.

22

For Building And Executing Queries

These are the buttons inside the Query group:

22

The first button on the left is Run. This option allows you to execute the DAX code you’ve written. And if you click the dropdown arrow, 2 more buttons will appear Run Query and Clear Cache and Run.

The next button is Cancel. Clicking this option will prompt the query execution to stop. This is especially useful if a query takes a long time to Run.

The Clear Cache option is similar to Clear Cache and Run, only that the former will simply clear caches in the program. So if you want to run the query after clearing the cache, it’s better to choose the latter option to reduce the number of clicks.

The Output option allows you to extract the results for viewing and editing in different software. For instance, if you want to view the results in Excel, you can use the Output option to extract the results as an Excel file.

33

The View group only has one button in it: the Query Builder.

The Query Builder allows you to execute a DAX query without needing to write any code.

44

After clicking the Query Builder, you only need to drag and drop columns and/or measures from your metadata pane.

The Edit group contains buttons similar to that of other programs, such as Excel.

5

These commands are applicable to the codes you’ve written.

The next group is Format:

55

Format Query is a great tool to use when writing DAX code. Clicking this will format your DAX codes according to DAX Studio standards.

This option uses a service known as daxformatter.com. It sends a request to this website, and in turn, the site formats the code and then sends it back to DAX Studio. Note that you need an internet connection to make this functionality work.

Next, the Comment and Uncomment buttons allow you to place comments in your DAX code.

The To Upper and To Lower buttons allow you to change the letter case of your written DAX code.

The Debug Commas option is a feature that allows you to shift commas from the end of the line to the start of the line. This is especially helpful when you’re writing multiple lines of code. It lessens the confusion.

The next group, Find, with the buttons Find and Replace, have similar functions to that of other software.

For Measuring And Monitoring Performance

Moving forward, there’s the Power BI group.

10

The Load Perf Data button allows you to import data that has been exported from the Power BI Performance Analyzer.

To do this, you first need to extract a file from Power BI. Go to the View tab in Power BI and click Performance Analyzer.

11

The Performance Analyzer pane will then appear. Click the Start recording button while going through the visuals and DAX code in Power BI you want to analyze, and then click Export.

12

Go back to DAX Studio and load the file you extracted from Power BI using the Load Perf Data option.

Next is the Tracesgroup:

T

The first button in this group is All Queries. When you click it, a new tab called All Queries will appear at the bottom.

Y

This tab contains Start Time, Type, Duration, User, Database, and Query. It provides digital statistics of the queries sent to the Analysis Services in either Power BI, Power BI Services or SSAS.

The Query Plan option provides information about the logical and physical query plan. These can help you understand how the DAX engine solves a DAX code. When you click this option, another new tab will appear at the bottom.

 

Ty

Then there’s the Server Timings option. It provides information on how data was extracted from the storage engine. Similarly, another new tab will open if you click it.

Tyyy

For Traces, you can’t have all three options turned on simultaneously.

If you try to click two options simultaneously, an error message will appear saying that you have to wait for the trace to update.

For Use With External Software

The last group under the Home tab is Connection

Tyy

The Connect button allows you to connect or change the current connection to either the Power BI model, Analysis Services, or Excel from within DAX studio.

For example, you’re working with a Power BI model, and you want to switch instantaneously between the SQL Server data tools or SSAS. Then, just use the Connect option and make the changes you need.

Tyyyy

The Refresh Metadata option, when clicked, will update your tables and data found in the Metadata pane in DAX Studio.

For example, you made changes in your data in Power BI. This change won’t be automatically tracked by DAX Studio. So, you need to click Refresh Metadata, and it will then update accordingly.

2. The Advanced Tab:

The second tab in the Ribbon is the Advanced tab.

 

T1

The options in this tab are grouped in 4 according to function.

First is the Metrics group:

Rrr

Let’s first discuss the View Metrics option.

When clicked, this will open the VertiPaq Analyzer Metrics pane that provides a lot of information on the Tables, Columns, and other data used in your current DAX Studio file.

3. Using The Metadata Pane In DAX Studio

The Metadata Pane provides you with information about your data mode

Dax

 

The first dropdown menu provides you with the name of the data model. If you’re connected to SQL Server Analysis Services, you’ll see multiple databases listed in this dropdown menu.

The search option allows you to filter out the contents of your data model, such as tables and columns.

Using The Functions Pane In DAX Studio

The Functions pane is used if you want to understand the parameters and argument of a given function.
5555

Drag and drop a function from the Functions pane to the coding area to view the parameters. In this example, it’s the DATE function.

1
You’ll then see the argument you need to make the DATE function work properly in DAX Studio.
Note: You can’t use reserved Keywords in your DAX code.
Daxxx

Conclusion

DAX Studio is one of the best tools to use when analyzing DAX queries in Power BI. So, it’s important to become familiar with each feature it has built with. It’s important to understand what they are, how they can be used, and how they can impact your workflow when using DAX Studio.

This overview of the user interface of DAX Studio is a good foundation before writing DAX codes.

Thank you for reading. Cheers!

Thoughts on “Introduction to the DAX Studio”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Arpit Malviya

Arpit Malviya has worked at Perficient as a technical consultant. He has been working in the Power BI & Data Analysis field since 2019.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Follow Us
TwitterLinkedinFacebookYoutubeInstagram