Instructor-led training -

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Power BI DAXPower BI DAX

Face to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.

From £460 List price £700

Training manual sample

Below are some extracts from our Power BI DAX manual.

What is DAX (Data Analysis eXpressions)?

DAX stands for Data Analysis Expressions. It is a formula language used to improve the speed and performance of data when it is being analysed in Power BI Desktop. There are 3 main outputs for creating any DAX formula:

Calculated Columns – similar to those created in Excel pivot tables

Measures – which mainly analyse numerical data 

Calculated Tables – where new tables can be created alongside the existing dataset

DAX is also used in other Microsoft Products, such as Power Pivot and SSAS Tabular, but this manual will focus on DAX in Power BI Desktop.

DAX structure and syntax

DAX formulas have many similarities to Excel formulas. For example, they they can contain nested functions, conditional statements, and value references However, there are two main differences to be aware of:

 

EXCEL

DAX

 

Formula starts with =

 

Formula starts with ‘Column Name’ =

OR ‘Measure Name =

OR ‘Table Name’ =

 

 

Formula uses cell references to build calculations

 

 

Formula references each row by a field name within a table eg. [Profit]


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:

 

 


 

Notice that each row is calculated in the ‘Product Type’ column to produce a new row listing as DAX recognises that all rows are included even though it only references the column name. When writing DAX, it is best practice to include the table name with the column name just for the sake of clarity. However, in this example the table name could have been omitted and the result would be the same:

AVA Product Type = [Product Type]&” (AVA)”

When referencing a table name, if there is a space between words then single quotes are required eg. ‘Premiums Table’. Otherwise, plain text will be fine 

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:

 

 

CALCULATED COLUMN

MEASURE

 

Storage

 

Physically resides inside a table

 

Has no ‘fixed abode’ and may be ‘stored’ within a ‘virtual table’ with other DAX measures

 

 

Performance

 

Slow as the extra column of data takes up memory when data is processed

 

 

Only takes up memory if used in a visual on a report so performance is better

 

When to use

 

When the new data created is non numerical eg. The ‘New Product Type’ column (as described earlier) could not be created using a measure

 

 

When datasets are huge, a measure is preferred over a column calculation

 

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’


 

Thanks. Your download will begin shortly.

Please help us

Share or create a link to this manual today!

Just follow these simple instructions...


Server loaded in 0.56 secs.