Data Analysis Expressions Archives

As the name suggests Data Analysis eXpressions or DAX in Power BI is nothing but collection of operators, functions and constants which we use in writing formula or expressions to return value/values. It is a native language for data analytics tools of Microsoft. DAX is also a highly versatile and functional language with the capacity to work with a relational database.

DAX helps us to dig into the data we already have in our hand to explore new information. It helps us to perform dynamic aggressions, slice and dice the data. It is different from Power Query with M language at its core. Power Query performs the data extraction from different sources. Whereas DAX is applied to the extracted data source for analysis purpose.

It is very common to confuse between DAX and Power Query. You can refer this article to know a detailed comparison between Power Query and DAX.

Excel formula is similar to the DAX formula. Anyone with experience in writing Excel formula finds it easy to write DAX formula. However, DAX is far advanced than the Excel worksheet formula.

DAX is mainly used to create “Measures” and “Calculated Columns”. Below is an example of creating a measure using DAX.

Example of DAX formula

Writing effective DAX formula is the key. An effective DAX formula helps us to get the most out of the data. Writing the DAX formula in Power BI is easy. Power BI DAX editor has a smart complete feature, which automatically prompts us with probable options.

Now let’s try writing a DAX formula to perform a simple calculation. I already have a data set in the Power BI desktop on the rainfall of different Indian subdivisions. The data was scraped from the web using the data scraping tool of Power BI. You can get the details of how to do it in this article.

Below is an example of how a DAX measure has been created on the Power BI desktop. The screenshots from my Power BI desktop shows the steps of creating a measure. The purpose of the measure is to create total annual rainfall.

First of all to create a new measure, right-click on the “Fields” pane of the Power BI desktop report/data window and then choose “New measure“.

The default name of the measure is “Measure“. I have changed it to “Rainfall“. As you start writing the function name Power BI starts suggesting with relevant functions name. Here I have selected “CALCULATE“. It is a very popular and frequently used function of DAX.

As we enter into the “CALCULATE” function, it starts to prompt us to show that it will accept an expression followed by filters. I have selected the “SUM” function and the “ANNUAL” column of the “rainfall_india” table inside it as we want to calculate the total annual rainfall.

With this, the measure has been created. We can check the “Rainfall” measure in the “Fields” pane under the “rainfall_india” table.

Nested function in DAX

Inside the “CALCULATE” function again I have chosen the “SUM” function. This is an example of a nested function, which is a function within another function. Nested functions help us to narrow down the query to achieve the desired result.

DAX can have up to 64 nested functions. Although using this many numbers of nested functions is very uncommon as debugging of such complex functions is very tough and the execution time of such functions is also high.

Using a measure in another measure

Another useful feature of the DAX formula is it allows using a measure already created within another measure. For example, if want to further narrow down the result to calculate the total annual rainfall of any particular subdivision, we can use the “Rainfall” measure we already created. Let’s see how to do it.

For example, we want to know the total annual rainfall of the state “Kerala“. The measure “Rainfall” calculates the total annual rainfall. So, we need to provide a filter within the calculate function along with the “Rainfall” measure.

See the above image where I have nested one measure within another. A table and a bar chart are also created to compare the total annual rainfall and Kerala_rainfall just show how the measures are performing.

Row context and filter context of DAX

These two concepts of context are very important for the effective use of DAX. Context refers to the dynamic analysis of the data.

Row context is related to functions while applying filters to identify a single row from the table. In most of the cases, we even dont realize that we are applying the concept of row context.

Filter context is a more complex concept than row context. It applies to narrow down the data. For example, here you can see how the column “SUBDIVISION” of “rainfall_india” has filtered the context and helped us to get the annual rainfall of a particular subdivision.

This article presents a discussion on Power query Vs DAX. As they can perform many similar tasks so it is very normal to get confused when to use which one. Except for some features in common these two are completely different in use and syntax.

Power Query performs the query from the source, through ETL process format and store the physical data tables in Excel or Power BI. DAX comes into the picture once the data is already queried from the source to calculate tables and different analysis.

Here we will briefly go through the concepts of these two with uses and example.

Power Query

It is a powerful query language and helps us build queries to mashup data. The name “M” of the M language which is actually behind the Power Query has come from “Mashup”. Power Query is available with Microsoft Excel 2016 Get & Transform and Power Query in Excel.

It is the M language syntax used in both Excel and Power BI Desktop. Mainly used for data Extraction, Transform and Load (ETL). ETL is an important step and allows us to start with our analysis tasks.

Power Query is used in both Excel worksheets as well as Power BI models.
It has lots of similarities in syntax with F# multi paradigm language which encompasses imperative, functional and object oriented programming methods.
Case sensitive and contains programming blocks with “Let" & “In“
The user may need a little programming experience in order to create advanced data mashup queries.

Power Query is used for query-time transformations in order to shape the data while extracting it.

Uses of Power Query

As mentioned, Power Query is basically for data extraction from the source. So, if we need any kind of data transformation operation to perform, then we will do it using Power Query before loading it into Power BI.

Particularly in Power BI desktop, if we are clicking “Transform” instead of “Load” we are using Power Query or M to make a required transformation in the source data.

In the above image, we have an example of Power Query, used for calculating the length of the target text.

Suppose the source data has two columns with First Name and Last Name. And we want to concatenate them to create one single name column, then we should use M or Power Query to do that.

It can also be done using DAX but it will require several lines of codes. So better to use M for creating a new column, Pivot or Unpivot in Excel etc during ETL itself.

DAX (Data Analysis Expressions)

DAX has few functions identical to Excel but many other functions too and way powerful than Excel functions. It is used for Power Pivot, summarize, slice and dice complex data. Unlike Power Query, DAX performs In-Memory transformation to analyze the extracted data.

DAX is a common language which can be found in SQL server analysis services Tabular, in Power BI and Power Pivot in Excel.

Use of DAX

As the name suggests, DAX is typically for data analysis tasks. After the data is already extracted, it is used for data modelling and reporting. Data analysis, slice and dice can be easily done with DAX.

It is very similar to Excel functions, does not have any programming blocks like M. So, any person has experience in Excel, can easily use DAX.

While creating Power BI report, DAX helps us to calculate Year to Date, calculate aggregates, ranges and several other analytical tasks easily with built in functions.

Here is an example of DAX in the above image. It is used here to create a new column for reporting. The new column calculated the total value of another column. Here you can notice the difference between the syntax of Power Query and DAX.

Final words on Power Query Vs DAX

Power Query and DAX are different. They are built with different purposes, they have different syntax and used in different stages.

Power Query is available both in Excel and Power BI. Users with Excel and the above version has this feature. Whereas DAX is exclusively available with Power BI.

The Data Analysis eXpression language is the far more advanced version of Excel worksheet functions. Still, DAX has many similarities with Excel functions hence easy to use as almost every one of us have experience of using Excel.

Both of them are required to use the Power BI platform to its full potential. You can not chose any one of them as they are specialized for different stages of a complete analytical tasks.

An overview of DAX in Power BI