DAX is an important query language used in Power BI that we’ve been learning during the last week of our training. While it’s been difficult to wrap our heads around it exactly, the hope is that as we continue to use it, we’ll get more and more comfortable with how and when to use the different functions that it has to offer.

There are some aggregation functions that are similar to the ones found in Tableau (which use the VizQL programming language, derived from SQL). These include, but are not limited to SUM, AVERAGE, DISTINCTCOUNT (written as COUNTD in Tableau), MIN, and MAX.

Calculate Function

One of the most important DAX functions is the CALCULATE function – you’ll probably start the majority of your longer and complex queries using this. You’ll want to use this when you want to apply filters to the context of the query, be it adding to or replacing. The syntax is as follows:

CALCULATE(<expression>, <filter 1>, <filter 2>, …)

Often times this will be used to find something based on the context of something else. For example, the image below will find the total profit, modified by the filter which, in this case, specifies to return the total profit of only the cookie type ‘Oatmeal Raisin’.

Divide Function

This function is quite self-explanatory, as it simply divides two columns. While you can still use the ‘/’ key to divide, this function helps clean up your DAX query as well as being able to automatically handle division by zero cases by returning a BLANK. The example below shows a case where you may want to use the DIVIDE function to find a percentage.

Contains Function

Another one with the purpose in its’ name, this function will return a ‘True’ if a certain expression is found in the listed columns, and a ‘False’ if they’re not. The example below uses the function to search for whether each order (each row) there is a return.

All Functions

There are a number of important versions of this function, two being ALL and ALLSELECTED. The ALL function (shown in the example for CALCULATE), will return all rows in the specified table or column, regardless of filters applied. This is very useful for calculating aggregations.

ALLSELECTED is similar except that instead of ignoring all filters, it will only ignore filters that are coming from the inner query, while keeping filters that are not row and column filters. The image below the query shows the difference in how the two functions work, with one returning the total grand profit of all cookies always, while ALLSELECTED returns only the total of all cookie types that have been selected in the filter.

Filter Functions

The FILTER function is similar to CALCULATE, except that it can only subset the data and applies a filter to a table. This means that only rows that are based on the filter expression will be returned in a table. The previous image in the ALLSELECTED function section showcases how only the row with the specified filter is returned while the others are blank, and the image below shows the DAX query.

Iterator Functions

The final type of function I’ll be discussing builds upon the simple aggregation functions mentioned at the start of this blog. These are known as Iterator Functions, or ‘X’ Functions, and are written by simply placing an ‘X’ behind the aggregation (e.g., SUMX, AVERAGEX, etc.). They are particularly important as they iterate through all the rows of a table, apply an expression, and then aggregate the result by the aggregation chosen. The main difference between a normal aggregation and an Iterator Function aggregation is the way it will compute an expression, rather than all the numbers in a column.

For an example of an iterator function, I have opted to showcase the AVERAGEX function to show the difference between the computing of an expression and the values of a column. The query below first performs the DIVIDE function, then takes the rows of the table (Orders) and executes the aggregation, which in this case is an AVERAGE function. The example in the table below the query shows the slight variations you will encounter when using either function.

Nicholas Seah
Author: Nicholas Seah