DAX Guide

DAX Guide — Complete Reference for Power BI

A comprehensive DAX (Data Analysis Expressions) guide for Power BI professionals. Learn DAX functions, formulas, best practices, and real-world examples to create powerful calculated measures and columns.

What is DAX?

DAX (Data Analysis Expressions) is a formula language used in Power BI, Power Pivot, and Analysis Services. It is designed for data modeling and business intelligence calculations, allowing you to create custom calculations, measures, and columns in your data models.

DAX Function Categories

Aggregation Functions

-- SUM
Total Sales = SUM(Sales[Amount])

-- AVERAGE
Avg Order Value = AVERAGE(Orders[Amount])

-- COUNT and COUNTA
Total Customers = COUNT(Customers[CustomerID])

-- MIN and MAX
Highest Sale = MAX(Sales[Amount])
Lowest Sale = MIN(Sales[Amount])

-- SUMX (iterator)
Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

Time Intelligence Functions

-- Year-to-Date
YTD Sales = TOTALYTD(SUM(Sales[Amount]), Dates[Date])

-- Month-to-Date
MTD Sales = TOTALMTD(SUM(Sales[Amount]), Dates[Date])

-- Previous Period
Prev Month Sales = CALCULATE(
    SUM(Sales[Amount]),
    PREVIOUSMONTH(Dates[Date])
)

-- Year-over-Year Growth
YoY Growth = 
DIVIDE(
    [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])),
    CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
)

-- Rolling 3-Month Average
Rolling 3M Avg = CALCULATE(
    AVERAGE(Sales[Amount]),
    DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]), -3, MONTH)
)

Filter Functions

-- CALCULATE (most important DAX function)
Sales in North = CALCULATE(
    SUM(Sales[Amount]),
    Region[Name] = "North"
)

-- ALL (remove filters)
Market Share = DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL(Products))
)

-- ALLEXCEPT
Relative Share = DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALLEXCEPT(Sales, Sales[Region]))
)

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

Logical Functions

-- IF
Sales Category = IF([Total Sales] > 100000, "High", "Low")

-- SWITCH
Performance = SWITCH(
    TRUE(),
    [Sales Growth] > 0.15, "Excellent",
    [Sales Growth] > 0.05, "Good",
    [Sales Growth] > 0, "Average",
    "Needs Improvement"
)

-- AND, OR
Premium Customer = IF(
    AND([Total Purchases] > 5000, [Years Active] > 2),
    "Premium", "Standard"
)

Text Functions

-- CONCATENATE
Full Name = CONCATENATE(Employees[FirstName], " " & Employees[LastName])

-- LEFT, RIGHT, MID
First 3 Chars = LEFT(Products[Code], 3)

-- UPPER, LOWER
Category = UPPER(Products[Category])

-- FIND and SEARCH
Has Email = IF(FIND("@", Contacts[Email], 1, 0) > 0, "Yes", "No")

DAX Best Practices

• Use measures over calculated columns for better performance
• Avoid using FILTER() with ALL() when ALLEXCEPT() will do
• Use variables (VAR) to improve readability and performance
• Avoid circular dependencies in your data model
• Test with DAX Studio for performance optimization

Example: Using Variables in DAX

Profit Margin = 
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR Profit = TotalRevenue - TotalCost
RETURN
DIVIDE(Profit, TotalRevenue, 0)

Want to learn more about DAX? Check out our blog articles for in-depth DAX tutorials and our free e-books for comprehensive learning resources.