Ranking Functions
Ranking functions assign ranks to rows based on specified criteria. Essential for Top N analysis and leaderboards.
RANKX()
Assigns a rank to each row in a table based on an expression.
Syntax: RANKX(Table, Expression, [Value], [Order], [Ties])
• Table: The table to rank • Expression: The formula to evaluate for ranking • Order: ASC (ascending) or DESC (descending, default) • Ties: DENSE or SKIP (how to handle tied values)
Example — Rank Products by Sales:
Product Rank = RANKX(ALL(Products), [Total Sales],, DESC, DENSE)Example — Rank Customers:
Customer Rank = RANKX(ALL(Customers), [Total Revenue],, DESC, SKIP)DENSE vs SKIP:
| Sales | DENSE Rank | SKIP Rank |
|---|---|---|
| 500 | 1 | 1 |
| 400 | 2 | 2 |
| 400 | 2 | 2 |
| 300 | 3 | 4 |
TOPN()
Returns the top N rows from a table based on an expression.
Syntax: TOPN(N, Table, Expression, [Order])
Example — Top 5 Products:
Top 5 Sales = CALCULATE([Total Sales], TOPN(5, ALL(Products), [Total Sales], DESC))Example — Top 10 Customers by Revenue:
Top 10 Revenue =
CALCULATE(
[Total Revenue],
TOPN(10, ALL(Customers), [Total Revenue], DESC)
)---
Context Modification: ALL() & REMOVEFILTERS()
REMOVEFILTERS()
An alias for ALL() when used inside CALCULATE. Clearer intent for removing filters.
Syntax: REMOVEFILTERS(Table or Column)
Example:
Total Regardless of Category =
CALCULATE([Total Sales], REMOVEFILTERS(Products[Category]))REMOVEFILTERS is identical to ALL inside CALCULATE — it's a readability improvement.
---
Using Variables (VAR)
Variables make DAX formulas more readable, maintainable, and performant.
Syntax
VAR VariableName = Expression
RETURN ResultExpressionWhy Use Variables?
• Readability — break complex formulas into named steps • Performance — expressions are evaluated once and reused • Debugging — swap RETURN to any VAR to test intermediate results • Maintainability — easier to update one variable than multiple references
Examples
Year-Over-Year Growth with Variables:
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
VAR Growth = CurrentSales - PreviousSales
RETURN
DIVIDE(Growth, PreviousSales, 0)Conditional Formatting Measure:
Sales Status =
VAR CurrentSales = [Total Sales]
VAR Target = [Sales Target]
VAR Achievement = DIVIDE(CurrentSales, Target)
RETURN
SWITCH(
TRUE(),
Achievement >= 1, "On Target ✅",
Achievement >= 0.8, "Close ⚠️",
"Below Target ❌"
)---
DAX Performance Optimization
Best Practices for Faster DAX
| Tip | Description |
|---|---|
| Use measures over calculated columns | Measures are evaluated on demand; calculated columns add to file size |
| Minimize iterator functions | SUMX, FILTER on large tables are slow — use simple aggregations when possible |
| Use variables | Avoid recalculating the same expression multiple times |
| Avoid bidirectional relationships | They slow down filter propagation |
| Use REMOVEFILTERS instead of ALL in CALCULATE | Clearer and sometimes optimized differently |
| Use Star Schema | Fewer relationships = faster DAX evaluation |
| Reduce table size | Remove unused columns in Power Query before loading |
| Avoid DISTINCTCOUNT on high-cardinality columns | Very memory-intensive |
| Use TREATAS instead of relationships when needed | For virtual relationships without model changes |
| Test with DAX Studio | Free tool for analyzing query performance |
Common Performance Anti-Patterns
• ❌ Nested FILTER inside CALCULATE on large tables • ❌ Using SUMX when SUM would suffice • ❌ Creating calculated columns for values that should be measures • ❌ Using COUNTROWS(FILTER(...)) instead of CALCULATE(COUNTROWS(...), ...) • ❌ Not using variables for repeated sub-expressions
Debugging DAX
• Use DAX Studio (free) to test and profile queries • Use Performance Analyzer in Power BI Desktop (View → Performance Analyzer) • Temporarily change RETURN to intermediate VARs to inspect values • Test measures with simple tables before using in complex visuals