How to use DAX Studio in Power BI

Power BI makes working with data really easy with real-time high-level analytics, extensive modeling, and custom development. However, when using Microsoft Power BI, you’ll frequently discover that you need to create formulae and expressions to analyze data and calculate values to solve real business problems. This is where DAX Studio Power BI comes in.

So, what is DAX Studio? It is a free client tool that allows you to perform advanced calculations and analysis on your Power BI data models using DAX queries. Data Analysis Expressions (DAX) is a collection of functions, operators, and constants used to create formulae and expressions in Power BI and various other Data Analysis services such as Microsoft SQL Server etc.

This blog will discuss various aspects of DAX Studio Power BI and help you learn to use it. Let’s get started.

What can you do with DAX Studio in Power BI?

• Learn DAX Language: DAX Studio helps you author DAX queries, analyze the performance of your data models, and learn the DAX language. To learn more about DAX, explore the Query Builder on the Home tab.
• Optimize your Model Performance: VertiPaq Analyzer is a tool integrated with DAX Studio that allows you to optimize your model performance easily. It provides an immediate report of the data distribution and memory consumption and helps you resolve the issues. You can also run a measure in DAX studio and leverage the Server Timings tool to see how the formula is interpreted.
• Visualize DAX “table” Functions: In DAX Studio, you can visualize the result of measures that contain a table function in DAX. This allows you to see the result table to check if the desired table is being produced. This functionality is not provided in Power BI Desktop.
• Extract your measures into a Spreadsheet: You can easily extract a list of your measures from your DAX Studio data model into a Spreadsheet, making it easy for you to document and reuse them.

To learn more about DAX in Power BI, see DAX Power BI.

Download, Install, and Setup DAX Studio Power BI

• Well, to get started you need to first download the latest version of DAX Studio. It’s an open-source tool and is available to download for free. Once the file is downloaded, just run the install routine.

• Upon running the installer, you can choose between “All Users” and “Current User” to proceed. It is recommended to use the default ‘All Users’ install option as it gives the richest user experience.

• Allow windows access to the app and accept the license agreement.
• Choose the destination location and click on “Next”.
• Choose the components you want to install and click on “Next”.

• Proceed with the installation and click on “Finish” once the installation process is complete.
• DAX Studio is now installed on your system. The next step is to connect it with Power BI. You can connect DAX Studio with Power BI by launching it directly from your Program Files within Windows.
• Upon launching the DAX Studio standalone application, you’ll be prompted to choose the type of connection. You can either connect it to a PBI file or a Tabular Server. You can connect to any of your opened Power BI Desktop files by selecting the correct data model.
• After selecting a connection type, click on “Connect”.
• You can skip the “Advanced Options” while connecting the PBI file.

DAX Studio UI Basics

Now that you’ve successfully connected DAX Studio Power BI, you can start working with the data models using the fascinating features of DAX Studio User Interface (UI). There is a lot to DAX Studio, let’s discuss a few important components of the UI to get started.

1. Metadata Panel
2. The Ribbon
3. Query Pane
4. Output Pane

Metadata Panel

The first thing you’re going to notice inside the DAX Studio is the metadata of your tables in your data model. This is the Metadata Panel and you can find all the tables, columns, and DAX measures in your data model here. Any table marked as a “Date Table” will have a clock icon displayed next to it.

The Ribbon

Next comes the ribbon from where you can access all the features of DAX Studio. Let’s discuss the various important options embedded in this ribbon.

1. This is the “Run” button that executes your query.
2. The “Clear Cache” button allows you to clear the cache for the current Database.
3. The “Output” button specifies the location where you want to send the query results. You can also change the default output format from the Output Pane to other accepted formats such as Excel or a file (CSV or TXT).
4. This option displays a drag and drop form of Query Builder.
5. The “Format Query” button leverages the DAX Formatter service to provide a nicely formatted query that is easier to read.
6. Clicking “Load Pref Data” will import the performance data from Power BI Performance Analyzer.
7. The “Connect” button shows the connection to the Power BI Desktop files. You can also click this button to connect DAX Studio to a different data model.
8. Clicking this button refreshes the metadata manually.

Query Pane

You can write, modify, format, and view your queries in the Query Pane.

Output Pane

This is the default Output Pane where the results of your query are displayed. It has 3 tabs:

• Output: Here, you can find general information about query run time.
• Results: This is simply a temporary storage location where the output table is returned after query execution.
• Query History: This displays the previously executed queries.

How to Write Queries in DAX Studio?

To start with DAX queries, you will need a data model inside your PBI file. Let’s discuss some of the important aspects of writing queries.

• Query Builder
• The EVALUATE Statement
• Extracting an Existing Table
• Filters

Query Builder

• Open DAX Studio and click on “Query Builder”. Clicking on it will open the Query Builder Pane.

• You can drag and drop columns and measures in this newly opened Query Pane. For the purpose of this demonstration, drop the “Fiscal Year“, “Category” columns, and the “Total Sales Amount” measure in the Columns/Measures area.

• Once you have dropped the columns and measures in the Query Pane, just click on the “Run” button and DAX Studio will generate the result under the Results tab.

Doing this, DAX Studio automatically writes your first query for you. You can click on “Edit Query” to see the code generated by Query Builder.

Every DAX query starts with the EVALUATE keyword. The most straightforward DAX query takes the form of EVALUATE <table expression>. Let’s delve a bit more into the EVALUATE statement.

The EVALUATE Statement

EVALUATE is a DAX statement containing a table expression and is a must for query execution. A query can also have multiple EVALUATE  statements depending on the requirements. The syntax of the EVALUATE statement is given below.

EVALUATE <table expression>

The <table expression> is the name of a table. It could be a DAX function capable of returning a table such as FILTER or SUMMARIZECOLUMNS. Let’s understand this better with an example of extracting a table of data.

Extracting an Existing Table

This is the most simple use case to help you get started, you can simply extract an existing table of data from your data model.

The query is very simple and easy to understand. It simply uses the EVALUATE statement followed by the name of the table you want to extract. For the purpose of this demonstration, the Product table is being extracted from a sample data model. 

EVALUATE
	Product

It is always a good practice to use line breaks in the Query Pane to space out things and improve the readability of DAX queries. The Product table is returned in the Results tab.

Filters

Moving on, you can get creative and start specifying filters on this sample data model to get specific data.

/* START OF QUERY BUILDER*/
EVALUATE
SUMMARIZECOLUMNS(
            ProductCategory[Category],
            Products[Brands],
            KEEPFILTERS( TREATAS( {"M"}, Customer[Gender] )),
            KEEPFILTERS( TREATAS( {"RED"}, Products[Color] )),
            "Total Sales", [Total Sales]
)
/* END OF QUERY BUILDER */

KEEPFILTERS is the keyword to apply filters. As you can observe, these filters specify the Gender (from Customer table) as “M” and Color (from Products table) as “Red”.

Click on “Run Query” to execute this and see the table returned under the Results tab.

Learn about eight popular DAX functions here.

DAX Studio Operators

The DAX language has four different kinds of calculation operators in formulas:

• Comparison operators compare values and deliver a logical TRUE/FALSE result.
• Arithmetic operators are used to execute arithmetic operations that yield numerical results.
• Text concatenation operations join more than two text strings.
• Logical operators combine multiple expressions to produce a single outcome.

DAX Studio Functions

• Aggregation functions: Aggregation functions, as described by the statement, compute a (scalar) value for each row in a column or table, such as the count, total, average, minimum, or maximum.
• Time and date functions: Microsoft Excel’s date and time functions and DAX’s date and time capabilities are comparable. All DAX operations, however, are predicated on a datetime data type that goes back to March 1, 1900.
• Filter functions: The DAX filter functions seek up values in linked narratives, return specified data types, and filter by related values. Similar to a database, the lookup functions operate by utilizing tables and relationships. You may generate dynamic computations by manipulating the data context using the filtering functions.
• Financial functions: Formulas that carry out financial computations, including net present value and rate of return, employ the financial functions in DAX. These features resemble Microsoft Excel’s financial features.
• Information functions: An information function determines if the value fits the anticipated type by examining the cell or row that is supplied as an input. For instance, if there is an error in the value you are referring to, the ISERROR function returns TRUE.
• Logical functions: When logical functions are applied to an expression, they return details about the values included within the expression. The TRUE function, for instance, informs you if an expression you are evaluating yields a TRUE value.
• Trigonometric and mathematical functions: The trigonometric and mathematical functions in Excel are quite comparable to those in DAX. The numeric data types that are utilized by DAX functions vary slightly.

Other functions

• Relationship functions: DAX relationship functions let you define the cross-filtering direction, choose a specific relationship to employ in an expression, and return data from another related table.
• Statistical functions: Values like standard deviation and number of permutations that are associated with statistical distributions and probability are computed using statistical functions.
• Text features: The text functions in DAX and Excel are quite similar. You may concatenate string values, search for text inside a string, and return a portion of a string. Additionally, DAX can manage the forms of integers, dates, and timings.
• Time intelligence functions: By utilizing the time intelligence tools that come with DAX, you may develop computations that leverage pre-existing knowledge about calendars and dates. 
• Functions for manipulating tables: These methods work with pre-existing or return tables. For instance, the SUMMARIZECOLUMNS function generates a summary table across a collection of groups, while the ADDCOLUMNS function adds calculated columns to a given table. 

Dax Studio Data types

Here are the data types supported by DAX:

Data type in model	Data type in DAX	Description
Whole Number	A 64 bit integer value	the number without any decimal places. Though they must be whole numbers between -9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1), integers can be either positive or negative.
Decimal Number	A 64 bit real number	the number without any decimal places. Though they must be whole numbers between -9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1), integers can be either positive or negative. Decimal places are allowed for numbers that are considered real. There is a large range of values in real numbers: Negative values ranging from -2.23E -308 to -1.79E + 308. Zero Positive numbers between 2.23E -308 and 1.79E + 308 Nevertheless, there can only be a maximum of 17 decimal digits that are relevant.
Boolean	Boolean	Either a True or False value.
Text	String	A data string of Unicode characters. can be expressed in a text format as strings, numbers, or dates.
Date	Date/time	Times and dates in a widely recognised date-time format. All dates post March 1, 1900 are considered valid.
Currency	Currency	The values in the currency data type range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, with a fixed accuracy of four decimal places.
N/A	Blank	In DAX, a blank is a data type that substitutes and represents SQL nulls. The BLANK function can be used to produce a blank, while the logical function ISBLANK can be used to check for blanks.

Conclusion

Power BI is all about Data Analytics, Data Visualization, and Business Intelligence. Data Analysis Expressions (DAX) is a formula expression language used in Power BI and other Analysis Services to perform analytics-specific tasks. And, DAX Studio is the best tool to analyze DAX queries in Power BI.

It’s a free open-source tool from SQLBI that allows you to write, execute, and analyze DAX queries.

This blog introduced you to Power BI and helped you understand how DAX Studio can add value to your analysis. Power BI makes Business Analysis more efficient through intuitive, interactive, and easy-to-use services.

FAQ on DAX Studio in Power BI