5.5 Essential DAX Patterns and Functions Reference

Quick Answer: Beyond core functions, the PL-300 tests practical DAX patterns. SWITCH(TRUE(), ...) replaces nested IFs. RANKX builds dynamic rankings. SELECTEDVALUE gets the single selected value. ISBLANK and COALESCE handle nulls. CONCATENATEX builds text lists. These patterns separate a pass from a fail on the DAX portion.

SWITCH

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

For range conditions, use the SWITCH(TRUE(), ...) pattern:

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

SWITCH(TRUE()) is preferred over nested IF because each condition is on its own line (readable), conditions are easy to add or remove (maintainable), there is no parenthesis matching (less error-prone), and conditions evaluate top to bottom, the first TRUE wins.

RANKX

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

Product Revenue Rank =
RANKX(ALL(Products[ProductName]), CALCULATE(SUM(Sales[Amount])), , DESC, Dense)

RANKX responds to filter context: in a matrix of products within categories it ranks within each category; slicers change which items are included. A frequent mistake is omitting ALL() so the rank denominator is filtered to a single row, producing a rank of 1 everywhere.

Handling Blanks and Empty Tables

Revenue Display = IF(ISBLANK(SUM(Sales[Amount])), "No Sales", FORMAT(SUM(Sales[Amount]), "$#,##0"))
Has Sales = IF(ISEMPTY(Sales), "No", "Yes")
Display Name = COALESCE([NickName], [FirstName], "Unknown")

ISBLANK tests a scalar; ISEMPTY tests whether a table expression has zero rows; COALESCE returns the first non-blank argument, equivalent to SQL COALESCE.

SELECTEDVALUE and HASONEVALUE

Selected Product = SELECTEDVALUE(Products[ProductName], "Multiple Products")
Chart Title = "Revenue for " & SELECTEDVALUE('Date'[Year], "All Years")

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

SELECTEDVALUE(col, alt) returns the single value when exactly one is in context, otherwise the alternate (BLANK if omitted). It is shorthand for IF(HASONEVALUE(col), VALUES(col), alt).

CONCATENATEX and FORMAT

Product List = CONCATENATEX(Products, Products[ProductName], ", ", Products[ProductName], ASC)
// Result: "Gadget, Gizmo, Widget"

Revenue Text = FORMAT(SUM(Sales[Amount]), "$#,##0.00")
Percentage Text = FORMAT([Margin], "0.0%")

Note that FORMAT returns text, so a formatted measure no longer sorts or aggregates numerically; prefer the column/measure Format property for display whenever the value must remain numeric.

On the Exam

The PL-300 tests SWITCH vs. nested IF, RANKX tie options, SELECTEDVALUE for single-value lookups, ISBLANK/COALESCE for nulls, and FORMAT for display.

These Patterns Show Up Everywhere

The functions in this section are the connective tissue of real reports. SWITCH(TRUE(), ...) builds segmentation and banding (performance tiers, price bands, RFM segments) far more cleanly than nested IFs. SELECTEDVALUE powers dynamic titles, what-if parameter reads, and "show the measure the user picked" patterns. RANKX drives Top-N visuals and league tables. COALESCE and ISBLANK keep visuals tidy by replacing blanks with sensible defaults.

Because they appear across modeling, reporting, and optimization scenarios, the PL-300 tests them not in isolation but woven into business problems, you are expected to recognize which utility solves a described need.

SELECTEDVALUE and the What-If Pattern

A recurring design uses a disconnected parameter table plus SELECTEDVALUE to make reports interactive. You create a calculated (or entered) table of values, expose it as a slicer, and read the user's choice with SELECTEDVALUE(Parameter[Value], default). A measure then multiplies or filters using that value, for example a discount-rate slider feeding a projected-revenue measure. SELECTEDVALUE is preferred over IF(HASONEVALUE(...), VALUES(...), ...) because it expresses the same intent in one readable function and safely returns the alternate when zero or multiple values are selected.

RANKX Pitfalls and CONCATENATEX Uses

Two practical cautions round out the toolkit. With RANKX, always rank over the right table scope, omitting ALL() so the rank table collapses to one row yields a rank of 1 everywhere, the classic "my ranking is all 1s" bug; and choose Dense vs. Skip deliberately depending on whether you want gaps after ties. CONCATENATEX is the go-to for turning a column of values into a readable, delimited, sorted string, useful for dynamic subtitles ("Showing: East, North, West") and for flattening a child list into a single cell of a matrix. Both functions reward knowing their optional ordering and tie arguments.

Reading the Scenario to Pick the Function

Most questions in this area describe a need and expect you to name the function. "Show a different label depending on which range a score falls into" is SWITCH(TRUE(), ...). "Display the selected item's name, or a placeholder when several are chosen" is SELECTEDVALUE. "Number products from highest to lowest revenue within their category" is RANKX over the right scope. "Substitute a default when a value is missing" is COALESCE; "hide a visual's value when there are no rows" leans on ISBLANK or ISEMPTY. "Build a comma-separated list of the regions in context" is CONCATENATEX.

Practicing this scenario-to-function mapping is more productive than memorizing syntax, because the exam rarely asks you to recall an argument order, it asks you to choose the right tool for a described outcome and to know its one or two important options (tie handling for RANKX, the alternate result for SELECTEDVALUE, the delimiter and sort for CONCATENATEX).