Power BI Blog: “X” and “Non-X” functions in DAX
30 September 2021
Welcome back to this week’s edition of the Power BI blog series. This week, we discuss the difference between “X” and “Non-X” functions in DAX.
DAX in both PowerPivot and Power BI includes numerous aggregation and statistical functions that are similar to formulas in Excel. Some of them end with an “X”. For example:
You may wonder how and when we can apply each of them. It’s simple:
- “Non-X” functions: only use a single column as a parameter. The syntax is as follows:
=FunctionName(column)
- “X” functions: specify a table, evaluate expressions for each row of that table (i.e. each record) and generate a result based on the set of values. The syntax is as follows:
=FunctionName(table, expression)
Therefore, if you already have a column with a set of values to calculate the result on, you can use “non-X” functions. For example:
Total Sales = SUM(Sales[Amount])
On the other hand, you should use “X” functions when you need to create a new set of values based on existing data. For instance:
Total Profit = SUMX(Sales, Sales[Revenue] - Sales[Cost])
You may also use them to apply some conditions before calculating. For example:
Total Profit of Product 1 = SUMX(FILTER(Sales, Sales[ProductID] = 1), Sales[Revenue] - Sales[Cost])
In this example, the SUMX function will store the calculated values of each row on a virtual column and then take the SUM of all rows in that column.
Check back next week for more Power BI tips and tricks!
