DAX Formulas - Advanced Calculations and Measures

What is DAX?

DAX (Data Analysis Expressions) is a formula language in Power BI for creating calculations.

Similarities to Excel:
- Similar functions: SUM, AVERAGE, etc.
- Logical operations: IF, AND, OR
- Text manipulation: CONCATENATE, UPPER, etc.

Differences:
- Works with tables and relationships
- Context-aware calculations
- More powerful for analytics

Creating Measures

Measures are calculations that aggregate data.

Create Measure:
1. Right-click table in Data View
2. New Measure
3. Write formula in formula bar
4. Press Enter
5. Measure appears in table

Example:
Total Sales = SUM(Sales[Amount])

Common Aggregate Functions

=SUM(Sales[Amount])                Sum all values
=AVERAGE(Sales[Amount])            Average value
=COUNT(Sales[OrderID])             Count rows
=COUNTA(Sales[Amount])             Count non-blank
=DISTINCTCOUNT(Sales[CustomerID])  Count unique
=MIN(Sales[Date])                  Minimum
=MAX(Sales[Amount])                Maximum

IF and SWITCH Functions

Conditional logic in formulas.

IF Statement:
=IF(SUM(Sales[Amount])>10000, "High", "Low")

Multiple Conditions:
=IF(
    AND(SUM(Sales[Amount])>10000, [Region]="North"),
    "High North",
    IF(SUM(Sales[Amount])>10000, "High Other", "Low")
)

SWITCH (Cleaner for many conditions):
=SWITCH(
    [Region],
    "North", "Region 1",
    "South", "Region 2",
    "East", "Region 3",
    "West", "Region 4",
    "Unknown"
)

Context Concepts

DAX works with row context and filter context.

Filter Context:
- Filters applied to visual
- All visible rows
- Affects calculations

Row Context:
- Current row in iteration
- CALCULATE, SUMX, FILTER
- More complex

Example:
Total = SUM(Sales[Amount])           [filter context]
Total This Row = Sales[Amount]       [row context]

CALCULATE Function

Override filter context.

Basic:
=CALCULATE(expression, filter1, filter2, ...)

Examples:
Total This Year = CALCULATE(
    SUM(Sales[Amount]),
    YEAR(Sales[Date]) = YEAR(TODAY())
)

Total North Region = CALCULATE(
    SUM(Sales[Amount]),
    Sales[Region] = "North"
)

Year-to-Date = CALCULATE(
    SUM(Sales[Amount]),
    DATESBETWEEN(Sales[Date], DATE(2025,1,1), TODAY())
)

Time Intelligence Functions

Handle date-based calculations.

Year-to-Date:
=TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

Month-to-Date:
=TOTALMTD(SUM(Sales[Amount]), Calendar[Date])

Previous Year:
=CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))

Year-over-Year Growth:
[Current Year Sales] - [Previous Year Sales]
Or: CALCULATE([Sales], DATEADD(Calendar[Date], -1, YEAR))

Lookup Functions

Find values in tables.

LOOKUPVALUE:
=LOOKUPVALUE(TargetTable[TargetColumn], 
    SearchTable[SearchColumn], SearchValue)

RELATED:
=RELATED(DimensionTable[ColumnName])
(Only works when relationship exists)

VALUES:
=VALUES(ColumnReference)
(Returns unique values in column)

Common Measure Patterns

Running Total:
=CALCULATE(SUM(Sales[Amount]),
    DATESBETWEEN(Calendar[Date], 
        BLANK(), 
        MAXX(ALLSELECTED(Calendar[Date]), Calendar[Date])))

Percent of Total:
=SUM(Sales[Amount]) / 
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))

Rank:
=RANKX(
    FILTER(ALL(Products), COUNTROWS(FILTER(Sales, Sales[Product] = Products[ProductID]))),
    SUM(Sales[Amount]), , DESC)

DAX Best Practices

Performance:
- Avoid complex nested formulas
- Use CALCULATE sparingly
- Test with large datasets
- Review query performance

Clarity:
- Use descriptive measure names
- Comment complex logic
- Break into multiple measures
- Consistent naming conventions

Testing:
- Create test visuals
- Verify results with sample data
- Check edge cases (nulls, zeros)
- Document assumptions

Debugging DAX

Tips:
- Start simple, add complexity
- Use CONCATENATE to check intermediate values
- Create helper columns
- Test with smallest dataset first
- Check data types

Error Messages:
- Read error carefully
- Check column references
- Verify relationships exist
- Look for circular dependencies