What is DAX Studio?
DAX Studio is an incredible free, open-source tool that allows you to directly query your Power BI / Power Pivot data models. It not only helps you to author DAX queries and analyze performance but also to learn the DAX language. DAX studio includes an editor that allows you to write and execute a query. Queries run in it return results as a table. In our Power BI training in Sydney and Melbourne and other Australian cities, we discuss this tool in detail. But if you have missed the Power BI training in Sydney or Melbourne, this blog will give you a step-by-step walk-through of DAX Studio. Darren Gosbell, an Australian MVP, created the first version of DAX studio, and then he donated it to the public domain. Since then, several developers have added features to it, but still, Darren is the main contributor. You can connect it to:- Power BI Desktop
- Power Pivot for Excel
- Tabular Servers
- Visual Studio
What can you do with DAX Studio?
- You can optimize your model performance with the VertiPaq Analyzer integrated with DAX Studio.
- Visually see the result of DAX formulas that contain ‘tables’ including ‘table expressions’. You cant see the result of such measures while doing it in Power BI Desktop, whereas in DAX studio you can explore what are the results being produced in this case.
- It also helps in authoring DAX measures as well as supports in learning DAX language. Query Builder is an incredible option in this case, available on Home tab.
Downloading DAX Studio
You can download the latest version of DAX studio from here. It’s a free tool and you can trust its source.
Operating system requirements
Following are the operating system requirements for running DAX Studio.
- Windows 10 (Recommended)
- Windows 8 / 8.1
- Windows 7
- Either 32 bit or 64 bit operating system is fine
The primary requirement for the operating system is that it supports the .Net Framework 4.7.1 or later.
Installing DAX Studio
After you run the installer, you will be prompted to choose one option between “All Users” vs. “Current User.” We recommend using the default ‘All Users’ install option as it gives the richest user experience, as you can see from the following table and is also the safer option.
After you choose install for all users you will be prompted by the windows to allow access to the app, and after you select Ok, you will see the license agreement. Please read and accept the license terms and click Next.
You will then be required to choose the destination location. Keep the default location as it is and press Next.
If you wish to install an Excel add-in keep it selected, otherwise uncheck it and press Next:
Another window will appear where you should press Next:
Select the ‘create a desktop shortcut’ option and select Next and click on the Install button on the following window:
DAX studio will be installed on your system and you will see the following window. Click Finish to complete the setup.
Connecting DAX Studio with Power BI
You can connect Power BI to DAX Studio in two ways.
Launching it directly from your system
If you run DAX studio from the desktop shortcut and open the list in front of the PBI / SSDT Model option, you will find all the Power BI desktop files that are open right now on your machine. You can see I have two PBI files open on my system, and to connect any of these, I need to select it and click Connect. There is no need to modify Advanced Options while connecting the PBI file.
Launching from Power BI Desktop
In the Power BI Desktop External Tools ribbon, DAX studio can be launched. When it’s done, PBI Desktop is automatically connected to the Analysis Services engine that operates as part of PBI Desktop, providing a seamless experience for users.
Whatever method you use, you can start working with the database using its features accessible from the user interface once you open DAX Studio. We are using DAX Studio 2.14.1 release, so what you see on your screen might differ slightly if you are using any other version.
We want to list down the most important options on DAX Studio user interface here.
1. Metadata panel
In the Metadata panel, you can find all the tables, columns and DAX measures in your data model. If you have marked a table as Date Table, a specific clock icon appears on it. It’s always a good idea to disable the auto Date/time option in Power BI File > Options and Settings > Options; otherwise DAX Studio will also show all the local date tables created automatically by Power BI.
2. The Ribbon
You can access all the features of DAX Studio through the ribbon. Here is the preview of its most important options.
Home tab
- Executes the query
- Clears the cache for the current database
- Output target to specify where you want to send the query results
- Displays a drag and drop query builder
- Uses com to provide a nicely formatted query
- Imports performance data from Power BI Performance Analyzer
- Shows the connection to the Power BI Desktop file
- Use to refresh the metadata manually
Advanced tab
- Display information and matrics about your model
- Exports data to CSV files or SQL Server tables
- Launch an Excel file connected to the current data source
Help tab
This tab is mainly about general info and help.
3. Query Pane
This is the query pane where you write, edit, format and view your queries.
4. Output, results and history pane
This pane has three tabs:
Output: General information on query run time.
Writing Queries in DAX Studio
The most straightforward DAX query is in the form of EVALUATE <table expression>. 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 statement
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’s a required keyword.
Query Builder
If you are in the path of 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 explain with an example. Suppose you want to have the total sales amount by fiscal year and product category; you can do so by the following method.
- Click on the Query builder.
- Place the fiscal year, product category columns and total sales amount measure in Columns/Measures area.
- If you want to add any filter you can do so in filter area.
- Press Run Query button.
- You will get the output in Results area.
- If you want to see the underlying query, click on Edit Query button.
- The query will be shown here.
Conclusion:
DAX Studio is an excellent tool and can be utilized in a number of ways to optimize the model and run DAX queries to name a few.
Learning Power BI from Scratch? Get a complete, in-depth training on Microsoft Power BI by attending our training courses.
More Checkouts for Power BI Training in Australia.
- Power BI Training in Sydney
- Power BI Training in Melbourne
- Power BI Training in Brisbane
