There are many DAX functions available in Power BI, but some are more frequently used than others. Understanding these functions can help you write flexible calculations and better understand how DAX works.
In this article, I will walk through some of the DAX functions and concepts that I find particularly useful in Power BI. I'll start with the X Family: a group of functions that perform calculations row by row before returning a result. Then look at ALL() and ALLSELECTED(), which affect the data used in a calculation. I'll finish with RELATED()a function that allows you to bring data from connected tables into your calculations.
The X family: Row-by-Row Calculations
I will refer to functions such as SUMX(), AVERAGEX(), RANKX(), COUNTX(), MAXX(), MINX() and others as the X Family. These functions share a common characteristic: they iterate through a table row by row, evaluate an expression for each row, and then return a result such as a sum, average, count, maximum, minimum, or rank.
The X Family functions are particularly useful when a simple aggregation is not enough. Instead of summing or averaging a single column, they allow you to perform a calculation for each row first and then aggregate the results. This makes them ideal for scenarios such as calculating revenue (Quantity × Price), ranking products, finding the highest order value, or calculating averages based on derived values.
Basic pattern:
FUNCTIONX([Table],[Expression])1.SUMX()Calculates an expression for each row and then sums the results. Useful when you need to calculate a value row by row before summing it.
Example
Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price] )
2.AVERAGEX() Calculates an expression for each row and then returns the average. Useful when you need the average of a calculated value rather than the average of a single column.
Example
Average Order Value =
AVERAGEX(Sales, Sales[Quantity]* Sales[Unit Price])
3.COUNTX()Counts non-blank results from an expression. Useful when you want to count rows where a calculated expression returns a value.
Example
Number of Orders with Revenue
= COUNTX( Sales, Sales[Quantity] * Sales[Unit Price] )
4.RANKX()This function is the slightly rebellious member of the X Family. While most X functions aggregate values, RANKX() focuses on comparing them, assigning a rank based on a measure or expression.
Example
Product Rank = RANKX(Product[Product Name], [Total Revenue] )
Product[Product Name]= the list of products to rank.
[Revenue] = the value used to rank them.
Now that we've explored how X functions perform calculations, let's look at how we can control which data is included in those calculations.
Filter Context: Data Used in a Calculation
1.ALL()Removes filters from a table or column, allowing a calculation to use all available data.
Example
Even if a user selects only Bikes and Accessories, every product is still included in the ranking.
Product Rank =
RANKX(ALL(Product[Product Name]),[Revenue])
2.ALLSELECTED()Returns all values selected through slicers and other external filters, while ignoring the filter applied by the current visual.
Example
If a user selects only Bikes and Accessories, the ranking is calculated using only those selected products. Products outside the selection are ignored.
Product Rank =
RANKX(ALLSELECTED(Product[Product Name]), [Revenue])
RELATED(): Working with Related Tables
RELATED() allows you to use data from a different table, as long as the tables are connected by a relationship. It follows that relationship to find and return the corresponding value.
Example
Revenue =
SUMX(Sales, Sales[Quantity] * RELATED(Product[Unit Price]))
RELATED() is often used alongside X family. While X family perform calculations row by row, RELATED() allows them to access data stored in related tables. Functions such as ALL() and ALLSELECTED() can then be used to control which rows participate in those calculations. This combination is common and useful in DAX, as it enables calculations to work across multiple tables while remaining flexible and responsive to filters.
Conclusion
DAX can seem overwhelming at first, but many calculations are built from a small number of core concepts. The X Family helps you perform row-by-row calculations, ALL() and ALLSELECTED() help you control filters, and RELATED() helps you work with data stored in different tables.
Once you understand how these functions work individually and together, you'll be able to create more flexible and powerful calculations in Power BI. I hope these examples have given you a good starting point and some ideas for your own reports.
Thank you for reading!