Introduction

This week was our (DSAU25) first exposure to Data Analysis Expressions (DAX). Being as it was our first exposure, there was ample opportunity to learn new methods of analysing data. Through writing this blog, I hope to explore some of the main concepts I have learned thus far in my journey with DAX. Whilst this blog will not cover the absolute basics of DAX, you can learn more from this introductory article by Microsoft. Lets get into it!

Calculated Columns

Calculated columns are a powerful feature in Power BI which allow to to calculate additional columns to already existing tables via DAX. The new created column will have properties associated with what the DAX code has defined for it. For example, in one of our practice examples we wanted to find the amount of profit that was made per sale of cookie (I know, quite a random dataset). Here, we already had two columns which contained the revenue generated per cookie, and also a column which contained the cost to produce the cookie. By calculating a new column, using the correct DAX code to take away the cost per cookie from the revenue, we were able to find the amount of profit made per cookie.

Measures

Measures are another way of doing calculations in power BI. The difference with measures however, is that they are used when you would like to aggregate the calculations across entire columns. These differ from calculated columns as they only calculate expressions at the row level. Utilising measures in our cookie example, we were able to find the total revenue, orders, and profit across the entire table. These measures all have a similar format, being first the name you would like to call the measure. Followed by the aggregation you would like to compute. Then inside the brackets of the aggregation, place the table and column you would like to act on.

Measures

Context

The CALCULATE() function allows you to evaluate a given expression in a modified filter context. For example, using the ALL() context modifier will allow you to return all rows from a table regardless of the filters currently on the visual. Moving back to our cookies example, we were to utilise the ALLSELECTED() modifier. Unlike the ALL() modifier, ALLSELECTED() will return all the rows of a table that meet the criteria of a filter. The example here was to return the grand total profit based on the cookies we specified in the filter. By adding the ALLSELECTED() modifier to our calculate function, we can alter the value based on the cookie type selected.

Iterators

Now iterators require two parameters to work. The first being the table that you wish to act on, and next the field in which you want the iterator to act on. In our cookies example, we needed to see if the month with the highest revenue on a transaction level, also had the highest order revenue. To get the maximum revenue at the transaction level, we created the following measure:

Measure

Then to show the maximum revenue at the order level, we created an iterator function as follows:

iterator

This would return the max revenue at the order level, rather than at the transaction/row level. Using both of these functions, we were able to generate a table and see that, the month and year with the highest revenue at the transaction level did not have the highest revenue at the order level.

DAX

Relationships

Relationship modifiers such as USERELATIONSHIP allow you to modify which relationship(s) you desire to be active. These relationships are the ones that exist between two tables, and can lead to vastly different outcomes in certain contexts. An example of this again is using the cookie data. Within the cookie data, we had two date fields, one for the order date (which was the active relationship) and the other for the ship date (which was the inactive relationship). So if we were needing to calculate anything associated with ship date, we would need to change the existing active relationship for that calculation:

Relationships

By using the USERELATIONSHIP function, we were able to change the active relationship to delivery date.

Time Intelligence

Time Intelligence functions allow users to calculate and compare aggregate data over time over periods of time. These periods can be over days, or even across years. Switching the data now to one associated with books, we wanted to determine the year to date sales for 2022. By using a time intelligence function (TOTALYTD), we were able to generate a running total for the total sales by month in 2022. In this function, we have specified the measure we would like to utilise, and the dates we are collecting the data from.

DAX

 

Overall, wrapping your head around DAX for the first time can be quite difficult, I know it was for me. However, given some repetition and exposure to DAX code, writing it becomes much easier. So don’t put your head down if it doesn’t come to you straight away, give yourself some time.

References

An article from Microsoft exploring DAX basics: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-quickstart-learn-dax-basics