In our Power BI DAX training across Australia and in our Power BI training blogs, we emphasize on the use of DAX studio for query building because DAX studio helps to understand the logical details of a query. DAX studio is a perfect editor for mastering DAX. In this blog post, a step-by-step guide to writing your first DAX query has been presented. But before you move further, we recommend you get DAX studio installed on your system and develop understanding of DAX studio interface.
If you’re new to DAX studio, here’s a list of blogs to assist your learning:
This blog serves as a guide for installing the DAX studio so that it can be used as a helper tool for query optimization in DAX.
Once the DAX studio is installed, it is important to understand the DAX studio navigation options so that the tool can be used efficiently. This blog is written to introduce the users with the DAX studio user interface.
Connecting DAX studio with Power BI
DAX Studio can be connected with Power BI using 2 methods:
To launch DAX Studio directly from your system:
- Open your Power BI desktop (.pbix) file.
- Click on DAX Studio shortcut on desktop.
A pop-up menu appears.
- Select the check box at the left of PBI/SSDT Model.
- Select the Power BI file from drop down.
- Click Connect.
To Launch DAX Studio from Power BI desktop:
- Open your Power BI (.pbix) file.
- Click on External tools in the top ribbon.
- Click on DAX Studio.
Now that the DAX studio is integrated with Power BI, lets begin by writing a simple query in DAX studio:
The most straightforward DAX query is in the form of EVALUATE.
The table expression can be as simple as the name of a table like in our case, it can be EVALUATE (Customers) or it could be a DAX function that returns a table such as FILTER or SUMMARIZECOLUMNS.
EVALUATE is a DAX statement containing a table expression and is needed to execute a query. A query can also have multiple EVALUATE statements, but the key point here is that it requires a keyword.
If you are learning DAX, or if you want to see how a query is created, Query Builder is the best option in DAX Studio. It provides a graphical user interface that makes it easier to build queries. Let’s use query builder to get total sales amount by product category.
To use DAX Studio for this:
- Click on the Home Tab in the Ribbon.
- Click on Query Builder in the View Section.
- Drag and drop Category Column from Products to Columns/Measures area
- Drag and drop Total Sales Amount (CM) from All Measures to Columns/Measures area
If you want to add any filter you can do so in filter area.
- Press Run Query
Notice the output in Results area.
- Click on Edit Query
Notice the automatic code generated in Query Pane.
Now that you have the basic knowledge to get started with DAX studio, here’s a blog to help you solve advance problems using DAX studio: Advanced scenarios with DAX Studio:
DAX is simple but its not easy. DAX revolves around a few important concepts which must be practiced regularly to master DAX. DAX studio proves to be a savior in this situation by providing an environment for easy query building. This blog provides a step-by-step guide to writing your first query in DAX studio.
More Checkouts for Power BI Training in Australia.