Power BI DAX Tutorial for Beginners

How to Write a DAX Formula

DAX formulas are intuitive and easy to read. This makes it easy to understand the basics of DAX so you can start writing your own formulas relatively quickly. Let’s go over the building blocks of proper DAX syntax. 

1. The name of the measure or calculated column
2. The equal-to operator (“=”) indicates the start of the formula
3. A DAX function
4. Opening (and closing) parentheses (“()”)
5. Column and/or table references
6. Note that each subsequent parameter in a function is separated by a comma (“,”)

DAX functions can also be nested inside each other to perform multiple operations efficiently. This can save a lot of time when writing DAX formulas. For example, it is often useful to have multiple nested IF statements or to use the IFERROR function to wrap around another function, so that any errors in the formula are represented by the value you specify. 

Some of the most common DAX functions used in reports are:

1. Simple calculations: COUNT, DISTINCTCOUNT, SUM, AVERAGE, MIN, MAX.
2. SUMMARISE: Returns a table typically used to further apply aggregations over different groupings.
3. CALCULATE: Performs an aggregation along with one or more filters. When you specify more than one filter, the function will perform the calculation where all filters are true.
4. IF: Based on a logical condition, it will return a different value for if it is true or false. This is similar to the CASE WHEN operation in SQL.
5. IFERROR: Looks for any errors for an inner function and returns a specified result
6. ISBLANK: Checks if the rows in a column are blank and returns true or false. Useful to use in conjunction with other functions like IF.
7. EOMONTH: Returns the last day of the month of a given date (column reference in a date format) for as many months in the past or the future.
8. DATEDIFF: returns the difference between 2 dates (both as column references in date formats) in days, months, quarters, years, etc.

Understanding Context in DAX Formulas

DAX formulas in Power BI are dynamic, and they change according to the context in which they were created. It’s important to have an understanding of how contexts work in DAX, as it can help save you a lot of headaches when you run into confusing errors in your formulas. 

There are two main types of context in DAX: row context and filter context.

Row Context

This refers to just “the current row” across all columns of a table and also extends to all columns in related tables too. This type of context lets the DAX formula know which rows to use for a specific formula.

Here is an example of a formula for a calculated column that has a row context:

Cost Price Per Unit = financials[COGS] / financials[Units Sold]

In this example, the calculation of Cost Price Per Unit is done on a row-by-row basis. This means that DAX needs to know what the current row is as it proceeds through the dataset, making the calculation and populating the new column with the result.

Row context is implicit in calculated columns. This is because the calculations that are performed in calculated columns are done on a row-by-row basis, and thus the row context is defined by default. However, this is not the case in measures since the aggregations are applied for all rows in a table. These calculations do not need to have any knowledge of a current row since all rows are aggregated together. 

As an example of a measure, consider the following DAX formula:

Profit margin = SUM ( financials[Profit] ) / SUM ( financials[Sales] )

In this case, the entire Profit column is summed to produce a single number, and this is divided by the sum of the entire Sales column. DAX does not need to know the current row, since it is performing an aggregation. Thus, this measure has no row context.

To explicitly define a row context in a measure, you need to use a special function called an iterator. Examples of iterator functions are SUMX, AVERAGEX, and COUNTX. These functions will first perform a calculation on a row-by-row basis, and then perform the final aggregation on the result (ie. sum, average, count, etc.). In this way, the row context is defined explicitly by the use of these iterators.

Let’s take a look at an example of an iterator function in action:

Average Cost Per Unit = AVERAGEX ( financials, financials[COGS] / financials[Units Sold] )

This example performs two calculations: first, the expression is evaluated on a row-by-row basis, and then the result is applied to the AVERAGE function. An alternative way of reaching this same result is to first create the calculated column ‘Cost Price Per Unit’ as we did above and then create a separate AVERAGE measure of that column. However, knowing when to use these iterator functions can make your reports more efficient and use up less memory as you are effectively able to perform two calculations using just a single formula.

Filter Context 

Filter context is applied on top of a row context and refers to a subset of rows or columns that are specified as filters in the report. Filters can be applied in a few ways:

• Directly in a DAX formula
• Using the filters pane
• Using a slicer visual
• Through the fields that make up a visual (such as the rows and columns in a matrix)

A good example of how to add a filter context to a DAX formula is to use the CALCULATE function, which allows you to add one or more filter parameters to the measure. In the example below, we create a profit margin measure filtered for USA only:

USA Profit Margin =

CALCULATE ( SUM ( financials[Profit] ) / SUM ( financials[Sales] ),  financials[Country] = "United States of America")

Conclusion

You now have some basic DAX tools to try out, and they are just the tip of the iceberg of all that DAX can do. With 250 possible functions to learn, the road to DAX mastery is both challenging and rewarding. We’ll cover more advanced functions in our Advanced DAX tutorial, and you can start taking your DAX skills to the next level today with our Dax in Power BI course. Or, you can read more about why Power BI is one of the best skills to learn here.