Common DAX Functions

// Five measures every model starts with
Total Revenue      = SUM(Orders[Revenue])
Order Count        = COUNTROWS(Orders)
Avg Order Value    = DIVIDE([Total Revenue], [Order Count])
Unique Customers   = DISTINCTCOUNT(Orders[CustomerID])
Total Cost         = SUM(Orders[Cost])
Gross Margin       = [Total Revenue] - [Total Cost]
Gross Margin %     = DIVIDE([Gross Margin], [Total Revenue])

// COUNT vs COUNTA vs COUNTROWS — the common confusion
-- COUNT(Orders[Revenue])    counts numeric non-blank Revenue values
-- COUNTA(Orders[Status])    counts non-blank Status values (text)
-- COUNTROWS(Orders)         counts all rows regardless of blanks
-- Use COUNTROWS for "how many orders?" — it is the most reliable

// IF(condition, result_if_true, result_if_false)
High Value Flag = IF([Revenue] >= 1000, "High", "Standard")

// IF with BLANK() — return nothing when condition not met
Late Flag = IF([Status] = "Late", "⚠ Late", BLANK())
// BLANK() keeps the cell empty in visuals — cleaner than ""

// Nested IF — use SWITCH(TRUE()) for 3+ branches instead
Tier = IF([Revenue]>=1500,"Platinum",IF([Revenue]>=1000,"Gold",
         IF([Revenue]>=500,"Silver","Bronze")))

// SWITCH(expression, val1, result1, val2, result2, ..., default)
Status Label =
    SWITCH([Status],
        "Delivered",  "✓ Complete",
        "Shipped",    "↗ Transit",
        "Processing", "⏳ Pending",
        "Late",       "⚠ Overdue",
        "? Unknown"
    )

// SWITCH(TRUE(), ...) for range conditions
Tier =
    SWITCH(TRUE(),
        [Revenue] >= 1500, "Platinum",
        [Revenue] >= 1000, "Gold",
        [Revenue] >= 500,  "Silver",
        "Bronze"
    )

// IFERROR(expression, value_if_error)
Safe Calc = IFERROR([Revenue] / [Quantity], BLANK())
// Catches any error in the expression — use sparingly
// DIVIDE() is better for division; IFERROR hides ALL errors

// AND() / OR() — function form (equivalent to && and ||)
Both Conditions = AND([Revenue] > 500, [Status] = "Delivered")
Either Condition = OR([Status] = "Late", [Status] = "Processing")

// ISBLANK(value) — TRUE only when value is BLANK
//   Note: ISBLANK returns FALSE for 0 and "" (empty string)
//   Use for: conditional display, null-safe arithmetic
Has Revenue    = NOT(ISBLANK([Revenue]))
Safe Revenue   = IF(ISBLANK(SUM(Orders[Revenue])), BLANK(), SUM(Orders[Revenue]))

// ISERROR(expression) — TRUE when expression produces any error
Safe Margin % = IF(ISERROR(DIVIDE([Margin],[Revenue])), BLANK(),
                   DIVIDE([Margin],[Revenue]))
// Prefer DIVIDE() with fallback over ISERROR — ISERROR catches too broadly

// ISNUMBER(value) — TRUE when value is numeric
// ISTEXT(value) — TRUE when value is text
// ISLOGICAL(value) — TRUE when value is TRUE or FALSE
Type Check =
    SWITCH(TRUE(),
        ISNUMBER([Revenue]),  "Number",
        ISTEXT([Revenue]),    "Text",
        ISBLANK([Revenue]),   "Blank",
        "Other"
    )

// HASONEVALUE(column) — TRUE when exactly one value is visible
//   in the current filter context for that column
//   Essential for measures that only make sense for a single selection
Selected Region Label =
    IF(
        HASONEVALUE(Orders[Region]),
        "Region: " & SELECTEDVALUE(Orders[Region]),
        "Multiple regions selected"
    )

// SELECTEDVALUE(column, alternateResult)
//   Returns the single selected value if exactly one is selected
//   Returns alternateResult otherwise
Current Year =
    SELECTEDVALUE(Calendar[Year], "All Years")
// Slicer has 2024 selected → "2024"
// No slicer selection → "All Years"
// Multiple years selected → "All Years"

// Concatenation
Full Name     = Customers[FirstName] & " " & Customers[LastName]
Order Label   = "Order #" & Orders[OrderID]

// CONCATENATEX — concatenate values across rows (with separator)
// Useful in a measure to list items for the current filter context
Selected Regions =
    CONCATENATEX(
        VALUES(Orders[Region]),   // distinct regions in context
        Orders[Region],           // value to concatenate
        ", "                      // separator
    )
// No filter: "North, South, West"
// Slicer = North: "North"

// LEFT / RIGHT / MID
Category    = LEFT(Orders[ProductCode], 4)      // "ELEC"
SKU         = RIGHT(Orders[ProductCode], 4)     // "1042"
MiddlePart  = MID(Orders[ProductCode], 3, 2)   // characters 3-4

// LEN — character count
Code Length = LEN(Orders[ProductCode])          // 9 for "ELEC-1042"

// UPPER / LOWER / PROPER
Normalised  = UPPER(Customers[Status])          // "DELIVERED"
Email       = LOWER(Customers[Email])           // lowercase all
Name        = PROPER(Customers[FullName])       // Title Case

// TRIM — removes leading and trailing spaces
Clean Name  = TRIM(Customers[FullName])

// SUBSTITUTE — replace text within a string
No Dash     = SUBSTITUTE(Orders[ProductCode], "-", "")  // "ELEC1042"
Redacted    = SUBSTITUTE(Customers[Email],
                  LEFT(Customers[Email],
                       FIND("@", Customers[Email]) - 1),
                  "***")                        // "***@domain.com"

// FIND vs SEARCH
// FIND: case-sensitive, returns position, errors if not found
// SEARCH: case-insensitive, returns position, errors if not found
At Position    = FIND("@", Customers[Email])    // position of "@"
Contains_UK    = NOT(ISERROR(SEARCH("uk", Orders[Region])))

// FORMAT — convert number or date to formatted text string
Revenue Str    = FORMAT([Total Revenue], "$#,##0.00")   // "$4,115.00"
Date Label     = FORMAT(MAX(Orders[OrderDate]), "MMM YYYY")  // "Feb 2024"
Pct Label      = FORMAT([Gross Margin %], "0.0%")           // "46.5%"

// Extraction — pull components from a date
Order Year    = YEAR(Orders[OrderDate])           // 2024
Order Month   = MONTH(Orders[OrderDate])          // 1, 2, ...12
Order Day     = DAY(Orders[OrderDate])            // 1..31
Order Quarter = QUARTER(Orders[OrderDate])        // 1..4
Order WeekNum = WEEKNUM(Orders[OrderDate])        // 1..53
Order Weekday = WEEKDAY(Orders[OrderDate], 2)     // 1=Mon..7=Sun (mode 2)

// TODAY() and NOW()
Days Since Order =
    DATEDIFF(Orders[OrderDate], TODAY(), DAY)
// Returns number of days between OrderDate and today
// Recalculates every time the report is opened

// DATEDIFF(start, end, interval)
// Intervals: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
Days To Deliver =
    DATEDIFF(Orders[OrderDate], Orders[DeliveryDate], DAY)

Months Since =
    DATEDIFF(Orders[OrderDate], TODAY(), MONTH)

// DATE(year, month, day) — construct a date from parts
First of Month =
    DATE(YEAR(Orders[OrderDate]), MONTH(Orders[OrderDate]), 1)

// EOMONTH(date, months_offset) — end of month
// 0 = end of same month, -1 = end of previous month, 1 = end of next
End of Order Month  = EOMONTH(Orders[OrderDate], 0)
End of Prior Month  = EOMONTH(Orders[OrderDate], -1)
End of Next Month   = EOMONTH(Orders[OrderDate], 1)

// DATEVALUE(text) — parse a text string as a date
Parsed Date = DATEVALUE("2024-01-15")   // returns a date type

// Compare dates
Is This Year =
    IF(YEAR(Orders[OrderDate]) = YEAR(TODAY()), "Current Year", "Prior")

// Rounding
Rounded Rev   = ROUND([Total Revenue], 2)      // standard rounding
Floor Rev     = ROUNDDOWN([Total Revenue], 0)  // always toward zero
Ceiling Rev   = ROUNDUP([Total Revenue], 0)    // always away from zero
Int Rev       = INT([Total Revenue])           // floor to integer
Truncated     = TRUNC([Total Revenue], 1)      // truncate, no rounding

// Absolute value and sign
Abs Variance  = ABS([Revenue] - [Budget])
Direction     = SIGN([Revenue] - [Budget])     // -1, 0, or 1

// Power and roots
Squared       = POWER([Value], 2)
Sq Root       = SQRT([Value])

// MOD and QUOTIENT
Remainder     = MOD([OrderID], 10)             // last digit of ID
Batches       = QUOTIENT([OrderID], 100)       // batch number

// Statistical
Std Dev       = STDEV.P(Orders[Revenue])       // population std dev
Variance      = VAR.P(Orders[Revenue])         // population variance

// RANKX(table, expression, value, order, ties)
-- Measure: rank current product by revenue within context
Revenue Rank =
    RANKX(
        ALLSELECTED(Products),   // rank across all selected products
        [Total Revenue],          // rank by this measure
        ,                         // value: blank = use current context
        DESC,                     // largest revenue = rank 1
        DENSE                     // no gaps in rank sequence
    )