Advanced DAX Patterns
Learn advanced DAX patterns for complex analysis
What You'll Learn
- Complex DAX patterns
- Performance optimization
- Real-world scenarios
- Best practices
Advanced Patterns
Running Total
Running Total = CALCULATE( [Total Sales], FILTER( ALL(Calendar[Date]), Calendar[Date] <= MAX(Calendar[Date]) ) )
Ranking
Rank = RANKX( ALL(Products[ProductName]), [Total Sales], , DESC )
Moving Average
3 Month Avg = CALCULATE( [Total Sales], DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -3, MONTH) )
Pareto Analysis
Top 80% = VAR CurrentProduct = MAX(Products[ProductName]) VAR AllProducts = ADDCOLUMNS( ALL(Products), "Sales", [Total Sales] ) VAR RankedProducts = ADDCOLUMNS( AllProducts, "Rank", RANKX(AllProducts, [Sales], , DESC) ) RETURN COUNTROWS(FILTER(RankedProducts, [Rank] <= 0.8 * COUNTROWS(AllProducts)))
Performance Tips
Use variables: Calculate once, reference many times
Avoid FILTER when possible: Use boolean filters instead
Materialize in Power Query: Static calculations better in Power Query
Test with small data: Verify logic before full dataset
Common Use Cases
Contribution %
Product Contribution = DIVIDE( [Total Sales], CALCULATE([Total Sales], ALL(Products)) )
Same Store Sales
Stores Open >1 Year = CALCULATE( [Total Sales], FILTER(Stores, DATEDIFF(Stores[OpenDate], TODAY(), YEAR) > 1) )
Customer Lifetime Value
CLV = CALCULATE( SUM(Sales[Amount]), ALL(Calendar), VALUES(Sales[CustomerID]) )
Next Module Preview
Module 5 covers Dashboard Design principles and best practices!
Congratulations!
You've completed Advanced DAX! You're now ready to build professional dashboards.
Tip: Advanced patterns take practice. Start simple, build complexity gradually!