Essential DAX Patterns and Functions Reference

Quick Answer: Beyond the core functions, the PL-300 tests practical DAX patterns. SWITCH(TRUE(), ...) replaces nested IFs. RANKX creates dynamic rankings. SELECTEDVALUE gets the single selected value. ISBLANK handles nulls. CONCATENATEX builds text lists. Mastering these patterns is the difference between passing and failing the DAX portion of the exam.

SWITCH Function

SWITCH is the DAX equivalent of a multi-way conditional:

Basic SWITCH

Region Label =
SWITCH(
    [RegionCode],
    "NA", "North America",
    "EU", "Europe",
    "AP", "Asia Pacific",
    "Other"  -- default value
)

SWITCH with TRUE() Pattern

For complex conditions (like nested IF but more readable):

Performance Band =
SWITCH(TRUE(),
    [Score] >= 90, "Excellent",
    [Score] >= 80, "Good",
    [Score] >= 70, "Satisfactory",
    [Score] >= 60, "Needs Improvement",
    "Unsatisfactory"
)

Why SWITCH(TRUE()) is better than nested IF:

• More readable — each condition is on its own line
• Easier to maintain — add/remove conditions without nesting
• Less error-prone — no matching of parentheses
• Evaluated top-to-bottom — first TRUE condition wins

RANKX Function

RANKX creates dynamic rankings:

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

// Rank products by revenue (highest = 1)
Product Revenue Rank =
RANKX(
    ALL(Products[ProductName]),    -- table to rank over
    CALCULATE(SUM(Sales[Amount])), -- expression to evaluate
    ,                              -- value (auto)
    DESC,                          -- order (DESC = highest first)
    Dense                          -- tie handling
)

Tie-Breaking Options

Dynamic Ranking

RANKX responds to filter context. In a visual:

• A matrix showing products by category will rank products within each category
• A card will show the rank of the currently filtered item
• Slicers affect which items are included in the ranking

Handling Blanks and Empty Values

ISBLANK

Revenue Display =
IF(ISBLANK(SUM(Sales[Amount])), "No Sales", FORMAT(SUM(Sales[Amount]), "\$#,##0"))

ISEMPTY

Tests if a table expression returns no rows:

Has Sales =
IF(ISEMPTY(Sales), "No", "Yes")

COALESCE

Returns the first non-blank value (like SQL COALESCE):

Display Name = COALESCE([NickName], [FirstName], "Unknown")

SELECTEDVALUE

Returns the single value in a column when the filter context has exactly one value:

SELECTEDVALUE(column [, alternateResult])

// Show selected product name, or "Multiple" if more than one is selected
Selected Product = SELECTEDVALUE(Products[ProductName], "Multiple Products")

// Dynamic measure title
Chart Title = "Revenue for " & SELECTEDVALUE('Date'[Year], "All Years")

HASONEVALUE

Returns TRUE if the filter context has exactly one value:

Dynamic Measure =
IF(HASONEVALUE(Products[Category]),
    [Revenue] / [Category Revenue],
    [Revenue] / [Total Revenue]
)

CONCATENATEX

Builds a delimited text string by iterating over a table:

Product List =
CONCATENATEX(
    Products,
    Products[ProductName],
    ", ",           -- delimiter
    Products[ProductName], ASC  -- sort order
)

Result: "Gadget, Gizmo, Widget"

FORMAT Function

Format values for display:

Revenue Text = FORMAT(SUM(Sales[Amount]), "\$#,##0.00")
Date Text = FORMAT(TODAY(), "MMMM DD, YYYY")
Percentage Text = FORMAT([Margin], "0.0%")

On the Exam

The PL-300 frequently tests:

• SWITCH vs. nested IF for multi-condition logic
• RANKX for dynamic rankings with different tie options
• SELECTEDVALUE for single-value context lookups
• ISBLANK and COALESCE for null handling
• FORMAT for display formatting in measures