DAX Formulas

-- Reference a column:  TableName[ColumnName]
-- Reference a measure: [MeasureName]
-- String: "text"     Number: 42   Decimal: 3.14
-- Boolean: TRUE()  FALSE()  BLANK()

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

-- Operators: + - * /  &(concat)  =  <>  < > <= &>=
-- Logical:   &&  ||  NOT()
-- IN operator
Sales[Region] IN {"North", "South"}

-- VAR stores an intermediate result
-- RETURN uses it — evaluated once (faster)

Profit Margin % =
VAR TotalSales  = SUM(Sales[Amount])
VAR TotalCost   = SUM(Sales[Cost])
VAR Profit      = TotalSales - TotalCost
RETURN
    DIVIDE(Profit, TotalSales)

-- DIVIDE(numerator, denominator [, alternateResult])
-- Safe division — returns 0 (or alt) instead of error

-- SUM: aggregates an existing column directly
Total Revenue = SUM(Sales[Revenue])

-- SUMX: evaluates expression ROW BY ROW first
Total Revenue X =
    SUMX(
        Sales,                              -- table to iterate
        Sales[Qty] * Sales[UnitPrice]       -- expression per row
    )
-- Use SUMX when the column you want doesn't exist

-- RANKX(table, expression, [value], [order], [ties])

Sales Rank =
    RANKX(
        ALL(Product[Name]),   -- table to rank over
        SUM(Sales[Amount]),   -- expression to rank by
        ,                         -- value (blank = current)
        DESC,                     -- DESC = 1 is highest
        DENSE                     -- DENSE = no gaps in rank
    )

-- SKIP (default): gaps after ties  1,1,3,4
-- DENSE:          no gaps           1,1,2,3

-- TOPN(N, table, expression, [order])
-- Returns a table of top N rows

Top 5 Revenue =
    CALCULATE(
        SUM(Sales[Amount]),
        TOPN(
            5,
            ALL(Customer),
            SUM(Sales[Amount])
        )
    )

-- Use in calculated column (row context)
-- Traverses a many-to-one relationship

-- In Sales table: get Category from Product
Category =
    RELATED(Product[Category])

-- RELATEDTABLE — from the "one" side
-- Returns a table of related rows
Order Count =
    COUNTROWS(RELATEDTABLE(Orders))

-- LOOKUPVALUE — no relationship needed
Manager Name =
    LOOKUPVALUE(
        Employees[Name],    -- return column
        Employees[ID],      -- search column
        Sales[ManagerID]    -- lookup value
    )

-- USERELATIONSHIP — activate inactive relationship
Delivery Sales =
    CALCULATE(
        SUM(Sales[Amount]),
        USERELATIONSHIP(
            Sales[DeliveryDate],
            Date[Date]
        )
    )

-- VALUES — distinct values (includes blanks)
VALUES(Product[Category])

-- DISTINCT — distinct values (excludes blanks)
DISTINCT(Product[Category])

-- HASONEVALUE — is only 1 value selected?
Safe Label =
    IF(
        HASONEVALUE(Product[Category]),
        VALUES(Product[Category]),
        "Multiple"
    )

-- EARLIER — reference outer row context
-- (in nested iterators)
Rank Col =
    COUNTROWS(
        FILTER(
            Sales,
            Sales[Amount] >
            EARLIER(Sales[Amount])
        )
    ) + 1

-- IF(condition, true, false)
Grade =
    IF(Students[Score] >= 90, "A",
    IF(Students[Score] >= 80, "B", "C"))

-- SWITCH — cleaner than nested IF
Grade =
    SWITCH(TRUE(),
        Students[Score] >= 90, "A",
        Students[Score] >= 80, "B",
        "C"   -- else
    )

-- IFERROR — catch errors
IFERROR(DIVIDE(Sales[A], Sales[B]), 0)

-- Text functions
CONCATENATE(C[First], " ", C[Last])
C[First] & " " & C[Last]  -- shorter
LEFT(C[Code], 3)           -- first 3 chars
RIGHT(C[Code], 2)          -- last 2 chars
MID(C[Code], 2, 3)         -- 3 chars from pos 2
LEN(C[Name])               -- length
UPPER(C[Name])             -- uppercase
TRIM(C[Name])              -- remove spaces
FORMAT(Sales[Date], "MMM YYYY")
FORMAT(Sales[Amount], "$#,0.00")

-- Math functions
ROUND(3.14159, 2)       -- 3.14
ABS(Sales[Variance])
INT(3.9)                -- 3 (truncate)
MOD(10, 3)              -- 1 (remainder)