Power BI Intermediate Level: 38 - DAX Measure Syntax and Best Practices

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: When writing complex DAX measures, it is best practice to use variables, write comments, and properly format the code. You can debug measures by temporarily returning the values of the variables which you are using.

As I mentioned in the previous article, each measure you create needs to belong to a home table but the choice of table is irrelevant. For that reason, many users prefer to add all measures to a designated formulas table. Let's first create such a table.

Creating a Formulas Table

In the Home ribbon, click Enter data, give the table the name “Formulas” (the table name “Measures” is reserved for Power BI), and click Load. The Enter data form is a quick and dirty way to create a table with manual input in Power BI. We use this to quickly create a table.

A table needs to have at least one column or at least one measure attached. So next, add a measure to the table. Select the Formulas table and click New measure in the Table tools context ribbon.

Let's create a measure named 38 Test Measure, which simply returns the number 1:

38 Test Measure = 1        

With one measure added, you can now go back to Power Query and delete the column in query Formulas. Then the Formulas table will be empty but have measures attached. It will rise to the top and have a little calculator symbol, indicating that it only contains measures. I am not sure why Microsoft makes it so difficult to set up this table but to the best of my knowledge, you have to use such a trick to create a table for just the measures.

DAX Measure Syntax

DAX measures have to return a single value. You already saw the most basic DAX syntax of measure name, an equals sign (=), and a value. Instead of a value, you can use a DAX function which returns a value, for example the COUNTROWS() function, which returns the number of rows of a table:

38 # Transactions = COUNTROWS('36_FACT_Transactions')        

While typing, the formula bar assists you with suggestions for auto complete for function names and arguments.

As another example, let's calculate the sum of the sales amount before discount column. Here we use the SUM() function, which takes a column as an argument.

After selecting a measure, you can change the formatting in the Measure tools context ribbon. Let's use it to format the sales amount measure as currency.

Code Formatting in DAX Measures

Your DAX formulas, even if you don’t have to write much, can get complex quite quickly. To aid understanding, you should make sure to use variables and proper formatting, including good variable names, comments, newlines and spacing. While you are still new to this, I recommend you to use https://www.daxformatter.com/ and paste your code there to get perfectly formatted code, then paste the formatted code back into Power BI. You can insert comments at the end of rows with two forward slashes “//”.

Variables in DAX Measures

For complex measures, I highly recommend to use variables, with which you can store and reuse results. This can improve performance, readability, and help with debugging. Let’s best look at the following example:

Select the Formulas table, and then on the Measure tools context ribbon select New measure. The DAX code from the screenshot creates three variables

• enumerator with value 2
• denominator with value 3
• result with dividing the enumerator and denominator variables

and returns the result variable.

You can use any variable names which are not reserved keywords. You can use as many variables as you like and have them depend on each other. Variables are denoted by VAR. Whenever you use variables, you must also use a return statement, usually returning the content of the last variable containing the end result.

Debugging of DAX Measures

When you want to test and debug your measures, you can temporarily change the return statement to output variables from the measure. In the example in the screenshot, I changed our measure to return the content of the enumerator variable. This way, you investigate issues.

Of course, you should change the measure back when you are done debugging. This debugging method only works for variables which return a scalar value.

I hope this gives you a good start for enabling you to write your own DAX measures. In the next article, I will teach you the basics of the most important and versatile DAX function: CALCULATE, with which you can change filter context of the report within the calculation.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?CALCULATE for Filtering within a Formula