Methods to create DAX measures

1. Methods to create DAX measures

Welcome back! Now that we are familiar with using DAX for calculated columns, let's look at using DAX for measures.

2. Implicit vs explicit measures

The first concept we need to understand is implicit vs explicit measures. Implicit measures are automatically created by Power BI and come directly from the database. An example would be if we drag Sales to values of a table, Power BI will automatically sum it. Using a dropdown menu we can define the aggregation: sum, min, average and so forth. Explicit measures is the name for explicitly written measures. An example would be if we calculate Total sales as the sum of all sales. Because of their flexibility explicit measures are preferred. Let's understand why.

3. Why explicit measures are preferred

First of all, it reduces confusion. If we see Sales on a report pane, we don't understand what it means exactly. It could be the sum, the average or something else. Secondly, they are reusable. We can refer to the Total Sales formula in another measure, such as a calculation for Total Sales East. Explicit measures can be given a custom name to explain its functionality. Being able to create specific functions for your needs reduces errors and will make maintenance of complex models more sustainable. You'll try this out during the exercises!

4. Best practices

Using explicit measures in Power BI can be very powerful, but it's important to do so in an organized and structured manner. The first tip is to keep DAX measures grouped together in a separate table. That is because measures are free to move to any table. This is in contrast with calculated columns, which belong to a specific table. The second tip is to format and comment with DAX. Using indentations you can increase legibility of your formulas, which becomes handy for more complex formulas or if you haven't looked at a formula for a while. You can also add comments after a double slash to explain what the function does.

5. Use variables to improve your formulas

Some measures might get complex quickly, but luckily we can use variables to make things easier to understand. You can store the result of an expression as a named variable, which can then be used as an argument for other measure expressions. They have four main advantages: improving performance, improving readability, simplify debugging and reducing complexity. You can define a variable by using VAR followed by the name and the expression. Finally we end with a return statement to move back to the main formula. Let's have a look at an example.

6. Use variables to improve your formulas - example

Let's calculate the absolute sales growth by using a variable. First we define the variable SALESPRIORYEAR. Using a calculate function we'll store the sales of the same period last year. This SAMEPERIODLASTYEAR() is a built in function in Power BI that looks at sales in the same timeframe exactly one year ago. Using SALESPRIORYEAR you can easily calculate the absolute growth in sales.

7. Use variables to improve your formulas - example

All together it looks like this. We can see the benefit of using a variable in this fairly basic function. But the power of using variables becomes even clearer when using more complex functions, which you'll discover in the exercises.

8. Let's practice!

It's practice time! Enjoy!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.