Power BI Lesson 32 – DAX Syntax | Dataplexa

DAX · Lesson 32

DAX Syntax in Depth

Understanding DAX syntax at the level of operators, data types, and function categories gives you the fluency to read any DAX formula — not just the ones you wrote yourself. This lesson covers every operator, how DAX handles type conversion automatically and when it does not, the full function category landscape, and the formatting conventions that make complex expressions readable months after you wrote them.

DAX Operators

Category	Operator	Meaning	Example → Result
Arithmetic	+	Addition	3 + 2 → 5
	-	Subtraction or negation	10 - 4 → 6 \| -[Revenue]
	*	Multiplication	[Revenue] * 1.1 → 10% uplift
	/	Division — errors if denominator is 0 or BLANK	10 / 0 → error · use DIVIDE()
	^	Exponentiation (power)	2 ^ 10 → 1024
Comparison	=	Equal to (also used for assignment)	[Status] = "Shipped" → TRUE/FALSE
	<>	Not equal to	[Status] <> "Cancelled"
	> < >= <=	Greater / less than (or equal)	[Revenue] >= 1000
	Logical	&&	Logical AND (also: AND() function)	[Rev]>0 && [Qty]>0
		\|\|	Logical OR (also: OR() function)	[Status]="Late" \|\| [Status]="Overdue"
		NOT()	Logical NOT — no operator shorthand, must use function	NOT([IsWeekend])
Text	&	Text concatenation — works on text only; converts numbers automatically	"Q" & "1" → "Q1"

DAX Data Types and Implicit Conversion

DAX has six data types. When you mix types in an expression, DAX attempts to convert automatically — sometimes helpfully, sometimes in ways that produce wrong results silently. Knowing the rules prevents unexpected blanks, wrong totals, and type mismatch errors.

DAX Type	Stores	Notes
Whole Number	64-bit signed integer	IDs, counts, year numbers. No decimal places stored.
Decimal Number	64-bit floating-point	Revenue, percentages, rates. Floating-point precision — avoid for financial exact-penny matching.
Fixed Decimal	Currency — 4 decimal places, exact	Use for monetary values where floating-point rounding is unacceptable.
Text	Unicode string	Names, codes, labels. Comparisons are case-insensitive in DAX — "UK" = "uk" returns TRUE.
True/False	Boolean	Result of comparison operators. TRUE = 1, FALSE = 0 when used in arithmetic.
Date/Time	Datetime — stored as decimal (integer = date, fraction = time)	Date arithmetic works with subtraction: [DeliveryDate] - [OrderDate] returns a decimal number of days.

// Implicit conversion — when DAX converts automatically

// 1. Text to Number — DAX converts "123" to 123 in arithmetic
//    Helpful: "123" + 1 → 124
//    Dangerous: "1,200" + 1 → ERROR (comma breaks the conversion)
//    Fix: use VALUE() to explicitly convert: VALUE("1200") + 1 → 1201

// 2. Number to Text — the & operator converts numbers automatically
"Q" & 1      → "Q1"       // 1 becomes "1"
"Year: " & 2024  → "Year: 2024"

// 3. Boolean to Number — TRUE/FALSE convert to 1/0 in arithmetic
TRUE + TRUE  → 2
FALSE * 5    → 0
// Useful pattern: count rows meeting a condition using SUMX
// Lesson 37 covers SUMX in depth — preview:
// SUMX(Orders, IF([Status]="Late", 1, 0)) counts late orders

// 4. BLANK behaviour — the most important implicit conversion rule
//    BLANK() + number    → number  (BLANK acts as 0 in arithmetic)
//    BLANK() & "text"    → "text"  (BLANK acts as "" in concatenation)
//    BLANK() = 0         → TRUE    (BLANK equals zero in comparison)
//    BLANK() = ""        → TRUE    (BLANK equals empty string)
//    BLANK() = BLANK()   → TRUE
// This means you cannot distinguish BLANK from 0 with = comparison
// Use ISBLANK() function instead:
ISBLANK([Revenue])          // TRUE only when blank, not when zero

// 5. Case insensitivity
"UK" = "uk"   → TRUE       // text comparisons are case-insensitive
"UK" = "UK "  → FALSE      // trailing space makes them different

The DAX Function Categories

DAX has over 250 functions organised into categories. Knowing which category to look in — and which functions are the workhorses of each category — is more useful than memorising individual function signatures. The category map below covers every area you will encounter in practical Power BI work.

Aggregation

The most-used category. SUM, COUNT, COUNTROWS, COUNTBLANK, DISTINCTCOUNT, MIN, MAX, AVERAGE, MEDIAN. These aggregate a column down to a scalar value within the current filter context.

SUM(Orders[Revenue])
DISTINCTCOUNT(Orders[CustomerID])
COUNTROWS(Orders)

Filter and CALCULATE

The most powerful category. CALCULATE modifies the filter context before evaluating its expression. FILTER, ALL, ALLSELECTED, ALLEXCEPT, KEEPFILTERS, REMOVEFILTERS, VALUES, SELECTEDVALUE.

CALCULATE(SUM([Revenue]), [Region]="UK")
ALL(Orders)
ALLSELECTED(Calendar[Year])

Time Intelligence

Requires a properly set-up Calendar table. DATEYTD, DATESYTD, TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESMTD, DATESQTD, PARALLELPERIOD, PREVIOUSMONTH, PREVIOUSYEAR.

CALCULATE([Total Revenue], DATEYTD(Calendar[Date]))
SAMEPERIODLASTYEAR(Calendar[Date])

Iterator (X functions)

Row-by-row calculation then aggregation. SUMX, AVERAGEX, COUNTX, MAXX, MINX, RANKX, PRODUCTX. The X suffix means "evaluate this expression for each row, then aggregate the results."

SUMX(Orders, [Revenue] * [Quantity])
AVERAGEX(Orders, [Revenue] - [Cost])

Logical

IF, IFERROR, SWITCH, AND, OR, NOT, ISBLANK, ISERROR, ISNUMBER, ISTEXT, ISLOGICAL, TRUE, FALSE. SWITCH is the clean alternative to deeply nested IF statements.

IF([Revenue]>1000,"High","Low")
SWITCH([Status],"Late","⚠","Shipped","✓","")

Text

CONCATENATE, CONCATENATEX, LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, UPPER, LOWER, PROPER, TRIM, FORMAT, VALUE, TEXT, FIXED.

CONCATENATE([First], " " & [Last])
FORMAT([Revenue], "$#,##0")
LEFT([ProductCode], 4)

Date and Time

DATE, YEAR, MONTH, DAY, TODAY, NOW, WEEKDAY, WEEKNUM, EOMONTH, DATEDIFF, DATEVALUE, EDATE, CALENDAR, CALENDARAUTO, FORMAT (for date strings).

YEAR([OrderDate])
DATEDIFF([OrderDate],[DeliveryDate],DAY)
TODAY()

Table Manipulation

FILTER, ALL, VALUES, DISTINCT, UNION, INTERSECT, EXCEPT, ADDCOLUMNS, SUMMARIZE, SELECTCOLUMNS, CROSSJOIN, TOPN, GENERATEALL.

FILTER(Orders, [Revenue]>1000)
VALUES(Calendar[Year])
TOPN(10, Orders, [Revenue])

BLANK() vs 0 vs Empty String

BLANK is DAX's null equivalent — it appears as an empty cell in a visual. The distinction between BLANK, zero, and empty string matters more in DAX than in Excel because implicit conversion rules make them look identical in some comparisons but behave differently in aggregations and visuals.

// How to detect blank vs zero correctly
IsBlankRevenue    = ISBLANK(Orders[Revenue])   // TRUE only if blank
IsZeroRevenue     = Orders[Revenue] = 0        // TRUE if 0 OR blank (!)
IsDefinitelyZero  = NOT(ISBLANK([Revenue])) && [Revenue] = 0

// Returning BLANK from a measure (best practice for "no data")
Safe Revenue =
    IF(
        ISBLANK(SUM(Orders[Revenue])),
        BLANK(),          // return BLANK, not 0 — keeps visual clean
        SUM(Orders[Revenue])
    )
// In a line chart: BLANK() creates a gap, 0 creates a zero point
// In a card visual: BLANK() shows nothing, 0 shows "0"
// Choose based on whether "no data" and "zero" mean different things

// BLANK propagation — BLANK in arithmetic produces BLANK
BLANK() + 1    → 1        // exception: arithmetic treats BLANK as 0
BLANK() * 5    → BLANK()  // multiplication propagates blank
BLANK() / 5    → BLANK()
5 / BLANK()    → ERROR    // division by blank = error, use DIVIDE()
DIVIDE(5, BLANK(), 0)  → 0   // safe division with explicit fallback

// COALESCE — return first non-blank value (DAX 2019+)
COALESCE([Revenue], [EstimatedRevenue], 0)
// Returns Revenue if not blank, else EstimatedRevenue, else 0

IF vs SWITCH — When to Use Each

IF handles two outcomes and can be nested for multiple branches. SWITCH is cleaner when checking the same expression against multiple values — it evaluates the expression once and matches against a list of value/result pairs, with an optional default at the end.

// IF — two outcomes, or nested for more
Tier =
    IF([Revenue] >= 1500, "Platinum",
    IF([Revenue] >= 1000, "Gold",
    IF([Revenue] >= 500,  "Silver",
                          "Bronze")))

// SWITCH — multiple outcomes from one expression (cleaner)
Tier =
    SWITCH(
        TRUE(),                        // TRUE() trick for range matching
        [Revenue] >= 1500, "Platinum",
        [Revenue] >= 1000, "Gold",
        [Revenue] >= 500,  "Silver",
        "Bronze"                       // default (no condition needed)
    )

// SWITCH for exact value matching (even cleaner)
Status Label =
    SWITCH(
        [Status],
        "Delivered",  "✓ Complete",
        "Shipped",    "↗ In Transit",
        "Processing", "⏳ Pending",
        "Late",       "⚠ Overdue",
        "? Unknown"   // default
    )

// SWITCH(TRUE()) vs nested IF — they produce identical results
// SWITCH(TRUE()) is preferred for 3+ branches because:
// 1. All conditions are at the same indent level — easier to scan
// 2. The default value is a single final argument — not buried in else
// 3. Each condition is evaluated top-to-bottom, stops at first TRUE

SWITCH(TRUE()) — Tier and Status Label results

OrderID	Revenue	Status	Tier	Status Label
1001	1,200	Delivered	Gold	✓ Complete
1002	850	Processing	Silver	⏳ Pending
1003	430	Late	Bronze	⚠ Overdue
1004	95	Late	Bronze	⚠ Overdue
1005	1,540	Shipped	Platinum	↗ In Transit

DAX Formatting Conventions

Well-formatted DAX is readable six months after you wrote it. Poor formatting turns a 10-line measure into a debugging puzzle. These conventions are not enforced by the engine — but every Power BI professional follows them.

// ── Convention 1 — measure name on its own line ────────────────
// Bad: Revenue % of Total = DIVIDE(SUM(Orders[Revenue]),CALCULATE(SUM(Orders[Revenue]),ALL(Orders)))

// Good:
Revenue % of Total =
    DIVIDE(
        SUM(Orders[Revenue]),
        CALCULATE(
            SUM(Orders[Revenue]),
            ALL(Orders)
        )
    )

// ── Convention 2 — one argument per line for multi-arg functions
// Bad: CALCULATE(SUM(Orders[Revenue]),FILTER(Orders,[Revenue]>1000),ALL(Calendar))

// Good:
High Value Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        Orders[Revenue] > 1000,
        ALL(Calendar)
    )

// ── Convention 3 — align SWITCH branches ──────────────────────
Tier =
    SWITCH(
        TRUE(),
        [Revenue] >= 1500, "Platinum",  // condition, result pairs
        [Revenue] >= 1000, "Gold",       // aligned for scanning
        [Revenue] >= 500,  "Silver",
        "Bronze"
    )

// ── Convention 4 — variable names with VAR / RETURN ────────────
// Use VAR to name intermediate values — like let...in in M
Revenue Growth % =
VAR CurrentRevenue   = SUM(Orders[Revenue])
VAR PreviousRevenue  = CALCULATE(
                           SUM(Orders[Revenue]),
                           SAMEPERIODLASTYEAR(Calendar[Date])
                       )
RETURN
    DIVIDE(
        CurrentRevenue - PreviousRevenue,
        PreviousRevenue
    )

// ── Convention 5 — comment complex logic ─────────────────────
Revenue % of Total =
    // Numerator: revenue in current filter context
    DIVIDE(
        SUM(Orders[Revenue]),
        // Denominator: total revenue ignoring all row filters
        // but respecting user's slicer selections
        CALCULATE(
            SUM(Orders[Revenue]),
            ALLSELECTED(Orders)
        )
    )

VAR and RETURN — Writing Readable Measures

VAR and RETURN are the DAX equivalent of intermediate variables. Instead of nesting five functions inside each other, you name each intermediate result with VAR, then combine them in the RETURN statement. Variables are evaluated once and reused — they also prevent the performance cost of evaluating the same sub-expression multiple times.

// Without VAR — hard to read, evaluates SUM twice
Revenue YoY % =
    DIVIDE(
        SUM(Orders[Revenue])
            - CALCULATE(SUM(Orders[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date])),
        CALCULATE(SUM(Orders[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date]))
    )

// With VAR — clear intent, SUM evaluated once per variable
Revenue YoY % =
VAR CurrentRev  = SUM(Orders[Revenue])
VAR PriorRev    = CALCULATE(
                      SUM(Orders[Revenue]),
                      SAMEPERIODLASTYEAR(Calendar[Date])
                  )
VAR Difference  = CurrentRev - PriorRev
RETURN
    DIVIDE(Difference, PriorRev)

// VAR rules:
// - Variables are immutable — cannot be reassigned
// - Variables capture the filter context at the point of definition
// - The RETURN expression can reference any VAR defined above it
// - Use RETURN on its own line — it marks the "output" clearly
// - VAR names: use PascalCase, no spaces, descriptive
//   Good: CurrentRevenue, PriorYearRevenue, GrowthAmount
//   Bad: x, temp, val1

Teacher's Note: The single formatting change that makes the biggest difference to DAX readability is using VAR and RETURN. A measure that fits on three lines with everything nested looks impressive to write but is painful to debug. The same measure with four named variables takes eight lines but anyone — including you three months later — can read what each step calculates. DAX is not a competition to write the shortest formula. It is a language you maintain. Write for the reader, not the compiler.

Practice

Practice 1 of 3

In DAX, text string comparisons are case-___ — the expression "UK" = "uk" returns TRUE.

Practice 2 of 3

Rather than nesting multiple IF statements for a Revenue tier calculation with four outcomes, the cleaner DAX pattern uses ___(TRUE(), ...) where each condition-result pair is listed at the same indent level.

Practice 3 of 3

To name intermediate calculation results inside a DAX measure for readability and to avoid evaluating the same sub-expression multiple times, you use the ___ keyword followed by a variable name and expression, then combine them after the RETURN keyword.

Lesson Quiz

Quiz 1 of 3

A measure returns 0 for some regions in a table visual, but you expect those cells to be blank because there were no orders. You check the formula and it ends with ... , 0) as the DIVIDE fallback. What is the fix and why does it matter visually?

Change the measure to use IF(SUM([Revenue])=0, BLANK(), ...) — wrapping in IF is required because DIVIDE cannot return BLANK directly Change the DIVIDE fallback from 0 to BLANK(): DIVIDE(numerator, denominator, BLANK()). In a line chart, BLANK creates a gap while 0 draws a flat zero line — using BLANK keeps the visual honest when there is genuinely no data for that period or region. The 0 result is correct — BLANK() and 0 are identical in DAX because BLANK() = 0 evaluates to TRUE. The visual appearance can be fixed by setting the "Show items with no data" toggle off in the visual's field well Replace DIVIDE with the / operator — the / operator returns BLANK() automatically when the denominator is zero, unlike DIVIDE which requires an explicit fallback argument

Quiz 2 of 3

You write a measure using VAR: VAR x = SUM([Revenue]) then later VAR x = x * 1.1 to apply a 10% uplift. What is wrong?

VAR names must start with an uppercase letter — renaming to VAR X resolves the conflict and allows reassignment The * operator requires both operands to be measures — wrap each VAR reference in a SUM() call: VAR x = SUM([Revenue]) * 1.1 DAX variables are immutable — you cannot redeclare a variable with the same name. Define a second variable with a different name: VAR BaseRevenue = SUM([Revenue]) then VAR AdjustedRevenue = BaseRevenue * 1.1. Variables defined inside a measure cannot reference other variables — each VAR must be a standalone expression referencing only column names and measure names, not other VARs defined earlier in the same measure

Quiz 3 of 3

A measure uses Orders[Revenue] = 0 to check whether revenue is zero, but it also returns TRUE for rows where Revenue is BLANK. How do you check for exactly zero without matching blank?

Use NOT(ISBLANK([Revenue])) && [Revenue] = 0 — first confirm the value is not blank, then check it equals zero. DAX's implicit conversion makes blank equal zero in comparisons, so the ISBLANK check is required to distinguish them. Use [Revenue] == 0 with double equals — the strict equality operator == in DAX distinguishes BLANK from zero, unlike single = which uses implicit conversion Use COALESCE([Revenue], -1) = 0 — replacing blank with -1 before comparing ensures the zero check only matches genuine zero values and not blanks There is no way to distinguish BLANK from zero in DAX — they are stored identically in the VertiPaq engine and any comparison treats them as the same value

Next up — Lesson 33 covers Measures vs Calculated Columns in full depth, including performance implications, when the model size tradeoff matters, and the complete set of scenarios where you must use one over the other.

← Previous Course Index Next →

Power BI Course