1. Creating DAX measures
In the demo, we’ll continue with our commodity dataset.
We’ll be diving deeper into DAX measures, applying iterators, and learning how to use variables in a calculate function.
In the last chapter, we created a calculated column to work out the difference between the opening and closing price.
Now we’ll create a measure that uses an iterator function to do the same calculation.
First, we’ll start by creating a new Measure called Average Trade Difference and start by typing AVERAGE which will use Power BI’s intellisense to show all matching functions.
We'll select AVERAGEX from the dropdown list.
The AVERAGEX function takes two arguments, the table where the aggregation will be applied, in this case commodities.
The second argument will be the calculation we are looking to perform on each row - in this case it’ll be Close subtracted by Open.
We can now commit the measure by pressing enter.
Let’s visualize this on a card - we can now see the average daily trade difference of 3.58 across all commodities.
Great! Now we’ve created our first measure using an iterator function.
Next, we are going to learn how to use the calculate function. These are one of the most used functions in DAX, due to it’s ability to modify and have priority over the filter context.
We’ll create a new measure called GoldVolume21. For this measure, we want to get the sum of all gold trades that occurred in 2021.
In our calculate function, our first argument will be the sum of volume. Now that we’ve added our expression to be calculated, we can now add the filters we want to apply by using the FILTER function.
The filter function takes the table to be filtered and the specific filter to be applied.
Our first filter will be on the table commodities where symbol is equal to Gold.
Next we’ll filter the same table again but this time filtering for the year 2021. For this, we will be using the YEAR function which we will cover in the next chapter.
Great job! Now we can visualize this new measure on a card - we can see the total gold volume for 2021 is 308 thousand.
The final concept we’ll be covering is a new topic we haven’t covered yet called variables.
Variables are very useful when writing DAX calculations.
As you write more DAX formulas, you’ll see that they can get complex very quickly.
Variables help simplify your code as well as increase readability and performance.
Variables can contain static values or snippets of code that may be re-usable in your query.
To get familiar with variables, we can look at using variables in conjunction with calculate.
We’ve created the measure GoldVolume21, but how about if we’d like to see the % growth in volume versus 2020.
A measure has been created called NoVariable. Let’s take a peek! This shows us a DAX formula that does not utilise Variables.
If you look closely you can see that the calculate function is being used twice.
This is inefficient for Power BI as it has to evaluate the same expression twice.
Let’s copy the formula as we’ll be using this in our new measure.
Let’s create a new measure that uses variables called Gold21vs20.
First, let’s create a variable called GoldVolume20. Here we can paste the formula we just copied. Now we have a variable that calculates Gold Volume for 2020.
We’ll now declare our RETURN statement that will compute our calculation.
We will use the DIVIDE function to workout the % difference.
For the numerator, we’ll calculate the difference between 2021 vs 2020. So GoldVolume21 minus GoldVolume20.
Next, we’ll set the denominator to be GoldVolume20.
We can now commit this measure and format it as a % and round it to 0.
Now we can put this into a card visual and see that there was a massive increase of 480% in trades of Gold.
We’ve seen variables, iterators and calculate in action. Now it’s your turn, let’s practice.
2. Let's practice!