Introduction to DAX

To illustrate the use of DAX, here is an example of how a calculated column can be created based on an existing column within the same table:

Why is DAX important?

When using Power BI Desktop to analyse the data, you can create many reports that provide the end user with a lot of Business Intelligence without any DAX required. However, an end user may want specific information that is only possible to obtain with DAX. For example, % sales growth for current year compared to previous year broken down by region or the number of days between order date and delivery date. A sound knowledge of DAX will really help the end user to solve many complex  problems in order to make informed business decisions.

There are other benefits to using DAX such as:

• When a visual does not allow the end user to fully analyse the data it represents eg. Geo maps not accurately mapping UK cities to their geographical locations

• Being able to re-use a DAX measure numerous times meaning that a calculation used in another calculation will not need to be reprocessed each time because the original calculation will have been stored in memory

• When a visual needs to display numerous measures one measure at a time depending on what is selected in a slicer eg. a line chart displaying either Profit, Sales Costs or Total Extended Price over time   

Calculated Columns versus Measures

Calculated Columns and Measures are the main ways to create DAX formulas and sometimes both methods can be used to get the same result. With a measure, the calculation has the same syntax and can reference tables, columns and set conditions just like with a calculated column. Where it differs is in the way it is stored within the Power BI dataset (or data model) which has an impact on data performance:

Whether a measure or calculated column is used depends very much on the data and how it needs to be manipulated.

Data Model

When using DAX on any dataset, it is important to think about how the dataset is structured and if there is a Data Model in place. In a very few cases, all data can come from a single data source where the data itself is represented as a ‘flat file’ ie. a single table containing all the data. In reality, the information is more often stored across a number of different tables where there is a common thread or a relationship between 2 tables. It is important to realise that the success of your DAX calculations depends on a robust data model where data from different tables can be used in the same formula. In fact, there are situations where data from different tables that are not ‘related’ via the Data Model can still be part of a calculation. A sound working knowledge of the Data Model and how it works is therefore essential to understand why DAX calculations behave the way they do

Visualisations

All DAX calculations, whether they are created using calculated columns or measures, can be used in all visualisations in a report. Depending on what fields are used in any given visual, a DAX formula can behave differently when summarising the data. For example, the data could show a breakdown of all quantities of orders for a particular division ie. ‘Enterprise’ for different categories such as Sales Reps and Clients. Table and Card visuals show the results below:

The same DAX formula produces different results because the fields that surround it in a visual are different. This is what is referred to as ‘Filter context’ and this concept will be explored later on when looking at specific DAX formulas. To use an analogy, think of each letter of the alphabet having different sounds depending on which letters surround it. So too does DAX behave differently within certain ‘filter contexts’