Introduction to DAX

// DAX syntax rules

// 1. Column references: TableName[ColumnName]
//    Always include the table name — unqualified [ColumnName] works
//    inside calculated columns only, and is ambiguous in measures
Orders[Revenue]          // correct in measures
[Revenue]                // works inside a calculated column in Orders
                         // avoid in measures — unclear which table

// 2. Measure references: just the measure name (no table prefix)
//    Measures live in the model, not in a table
Total Revenue            // reference an existing measure
[Total Revenue]          // square brackets are optional for measures

// 3. Text strings: double quotes only
"United Kingdom"         // correct
'United Kingdom'         // WRONG — single quotes mean table/column names in DAX
'Orders'[Revenue]        // single quotes wrap table names with spaces

// 4. Numbers: no quotes, no commas
1000                     // correct
"1000"                   // this is text, not a number
1,000                    // WRONG — comma is the argument separator

// 5. Comments
// Single-line comment
/* Multi-line
   comment */

// 6. Line breaks are allowed anywhere — use them for readability
Total Revenue =
    SUM(
        Orders[Revenue]
    )

// Scenario: Understanding the context difference

// As a CALCULATED COLUMN on the Orders table:
Revenue Per Unit = Orders[Revenue] / Orders[Quantity]
// Row context is active — this divides THIS row's Revenue by THIS row's Quantity
// Result per row: 300, null (0 qty), 215, 95, 256.67

// As a MEASURE:
Revenue Per Unit = SUM(Orders[Revenue]) / SUM(Orders[Quantity])
// No row context — cannot reference Orders[Revenue] directly as a scalar
// Must aggregate first: sum all Revenue, sum all Quantity, divide
// Result: 4115 / 13 = 316.5  (grand total)
// Result when filtered to Order 1001: 1200 / 4 = 300

// The column reference [Revenue] in a measure does NOT mean "this row's Revenue"
// It means nothing without an aggregation — DAX will return an error
// This is the #1 beginner mistake: treating measure code like Excel cell references

// WRONG measure (returns error or entire column sum unexpectedly):
Revenue Per Unit = Orders[Revenue] / Orders[Quantity]
// Without SUM(), DAX doesn't know which row's values to use

// CORRECT measure:
Revenue Per Unit = DIVIDE(SUM(Orders[Revenue]), SUM(Orders[Quantity]))

// The five measures every model needs — write these first

// 1. Total Revenue — aggregates all revenue in current filter context
Total Revenue = SUM(Orders[Revenue])

// 2. Order Count — counts rows (not distinct, all rows)
Order Count = COUNTROWS(Orders)

// 3. Average Order Value — safe division to handle zero denominator
Avg Order Value = DIVIDE(SUM(Orders[Revenue]), COUNTROWS(Orders))
// DIVIDE(numerator, denominator) returns BLANK() if denominator = 0
// Never use / for measures that might divide by zero — use DIVIDE()

// 4. Distinct Customer Count
Unique Customers = DISTINCTCOUNT(Orders[CustomerID])

// 5. % of Total — revenue as a percentage of grand total
// ALLSELECTED removes filters from Orders while keeping slicer context
Revenue % of Total =
    DIVIDE(
        SUM(Orders[Revenue]),
        CALCULATE(SUM(Orders[Revenue]), ALLSELECTED(Orders))
    )