Top 10 Essential DAX Formulas for Power BI Data Analytics

DAX (Data Analysis Expressions) is Power BI's formula language for creating powerful calculations. As a data analyst, mastering these 10 DAX formulas will help you solve 90% of analytics scenarios.

1. SUM()

What it does: Adds up all numbers in a column
When to use: Sales totals, revenue aggregations

Total Sales = SUM(Sales[Amount])

2. CALCULATE()

What it does: Modifies filter context
When to use: Conditional calculations

West Coast Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    Customers[Region] = "West Coast"
)

3. FILTER()

What it does: Creates a filtered table
When to use: Complex filtering scenarios

High Value Customers = 
CALCULATE(
    COUNTROWS(Customers),
    FILTER(
        Customers,
        Customers[LifetimeValue] > 10000
    )
)

4. RELATED()

What it does: Pulls data from related tables
When to use: When you need columns from another table

Product Category = RELATED(Products[Category])

5. DATEADD()

What it does: Shifts dates for time intelligence
When to use: YoY comparisons

Sales Previous Year = 
CALCULATE(
    SUM(Sales[Amount]),
    DATEADD(Calendar[Date], -1, YEAR)
)

6. DIVIDE()

What it does: Safe division (handles divide-by-zero)
When to use: Calculating ratios

Profit Margin = 
DIVIDE(
    SUM(Sales[Profit]),
    SUM(Sales[Revenue]),
    0  -- Return 0 if error
)

7. SWITCH()

What it does: Conditional logic (like Excel's IFs)
When to use: Categorizing data

Performance Category = 
SWITCH(
    TRUE(),
    [Sales] > 100000, "Excellent",
    [Sales] > 50000, "Good",
    "Needs Improvement"
)

8. AVERAGEX()

What it does: Row-by-row average
When to use: Weighted averages

Avg Sale per Customer = 
AVERAGEX(
    Customers,
    [Total Sales]
)

9. RANKX()

What it does: Ranking values
When to use: Top N analysis

Sales Rank = 
RANKX(
    ALL(Products),
    [Total Sales]
)

10. TOTALYTD()

What it does: Year-to-date aggregation
When to use: Running totals

YTD Sales = 
TOTALYTD(
    SUM(Sales[Amount]),
    Calendar[Date]
)

Pro Tips for DAX in Power BI

1. Use variables for complex formulas:daxCopySales Variance = VAR CurrentSales = SUM(Sales[Amount]) VAR PriorSales = CALCULATE(SUM(Sales[Amount]), DATEADD(…)) RETURN CurrentSales – PriorSales
2. Always filter first in CALCULATE for better performance
3. Use DAX Studio to debug complex measures

When to Use Which Formula

Next Steps

1. Practice these in a sample Power BI file
2. Learn context transition with [EARLIER()]
3. Explore iterator functions (MAXX, MINX)

Want a sample dataset to practice? Download AdventureWorks for Power BI