DAX & Measures

4. DAX — Data Analysis Expressions

DAX is Power BI's formula language. Understanding DAX is the difference between an average and an excellent Power BI user.

4.1 Calculated Columns vs Measures

Feature	Calculated Column	Measure
When calculated	At data refresh (stored in table)	At query time (on the fly)
Memory	Uses memory (stored per row)	Minimal memory
Context	Row context — access to current row values	Filter context — aggregates based on current filters
Use when	You need a value for each ROW	You need an AGGREGATION that changes with filters
Example	Profit = [Revenue] - [Cost]	Total Revenue = SUM(Sales[Revenue])

🧠 Simple rule: If you need it per row (like calculating profit for each order) = Calculated Column. If you need it as a total/average that changes with slicers = Measure.

4.2 Essential DAX Functions

Basic Aggregation

Total Revenue = SUM(Sales[Revenue])
Total Orders = COUNTROWS(Sales)
Avg Order Value = DIVIDE(SUM(Sales[Revenue]), COUNTROWS(Sales), 0)
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

🧠 Always use DIVIDE() instead of / — DIVIDE handles division by zero gracefully.

CALCULATE — The Most Powerful Function

CALCULATE allows you to modify the filter context of a calculation. It's the most important DAX function.

Syntax: CALCULATE(expression, filter1, filter2, ...)

// Total Revenue (responds to all report filters)
Total Revenue = SUM(Sales[Revenue])

// Revenue for Electronics ONLY (ignores category slicer)
Electronics Revenue = 
    CALCULATE(
        SUM(Sales[Revenue]),
        Products[Category] = "Electronics"
    )

// Revenue for 2024 ONLY
Revenue 2024 = 
    CALCULATE(
        SUM(Sales[Revenue]),
        DateTable[Year] = 2024
    )

// % of Total (powerful comparison)
Revenue % of Total = 
    DIVIDE(
        SUM(Sales[Revenue]),
        CALCULATE(SUM(Sales[Revenue]), ALL(Products)),
        0
    )

🧠 CALCULATE = "is number ko calculate karo, WITH THESE extra conditions." All other DAX builds on this function.

Time Intelligence (Very Frequently Asked)

These functions require a proper Date Table in your model.

// Year-to-Date Revenue
YTD Revenue = TOTALYTD(SUM(Sales[Revenue]), DateTable[Date])

// Previous Year Revenue (for comparison)
PY Revenue = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(DateTable[Date]))

// Year-over-Year Growth
YoY Growth % = 
    VAR CurrentYear = SUM(Sales[Revenue])
    VAR PreviousYear = [PY Revenue]
    RETURN
        DIVIDE(CurrentYear - PreviousYear, PreviousYear, 0) * 100

// Month-to-Date
MTD Revenue = TOTALMTD(SUM(Sales[Revenue]), DateTable[Date])

// Running Total (Cumulative)
Running Total = 
    CALCULATE(
        SUM(Sales[Revenue]),
        FILTER(
            ALL(DateTable),
            DateTable[Date] <= MAX(DateTable[Date])
        )
    )

🧠 Time intelligence = interview mein zaroor aayega. YTD, PY, YoY Growth — yeh teen ratt lo.

Iterator Functions (Row-by-Row Calculations)

Function	What It Does
SUMX(table, expression)	Calculates expression for each row, then sums
AVERAGEX(table, expression)	Calculates expression for each row, then averages
MAXX(table, expression)	Calculates expression for each row, returns max

// Weighted Average Price (can't do this with simple SUM/AVG)
Weighted Avg Price = 
    SUMX(
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    ) / SUM(Sales[Quantity])

4.3 ALL, ALLEXCEPT, REMOVEFILTERS

Function	What It Does	Use Case
ALL(table/column)	Removes all filters	Calculating % of grand total
ALLEXCEPT(table, column)	Removes all filters EXCEPT specified column	% of category total
REMOVEFILTERS(column)	Same as ALL but clearer intent	Modern replacement for ALL

// % contribution of each product to its category total
Category Contribution % = 
    DIVIDE(
        SUM(Sales[Revenue]),
        CALCULATE(
            SUM(Sales[Revenue]),
            ALLEXCEPT(Products, Products[Category])
        ),
        0
    )

---