Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 56 ยท DAX

DAX Logical and Information Functions

Master DAX logic: IF, SWITCH(TRUE()), AND/OR, COALESCE, IFERROR, ISBLANK, HASONEVALUE, ISFILTERED, ISINSCOPE and USERNAME, with runnable Python stand-ins.

Every nontrivial measure makes decisions. Show a value only when one product is selected, fall back to zero when nothing matches, label a customer "Gold" or "Silver", switch behavior at the total row of a matrix, or detect who is looking at the report. DAX does this with logical functions (IF, SWITCH, AND, COALESCE) and information functions (ISBLANK, HASONEVALUE, ISFILTERED, ISINSCOPE, USERNAME) that inspect values and filter context.

The code is read-only DAX, because Power BI cannot run on this site. Python stand-ins reproduce the decisions so you can see the rules at work. The most important idea in the lesson is the difference between blank, zero and an empty string, which DAX treats in a way that surprises almost everyone.

IF and SWITCH

DAX
Size Band =
IF ( Product[Price] >= 100, "Premium", "Standard" )

Weekday Type =
SWITCH ( WEEKDAY ( 'Date'[Date], 2 ), 6, "Weekend", 7, "Weekend", "Weekday" )

Customer Tier =
SWITCH (
    TRUE ( ),
    [Lifetime Sales] >= 10000, "Gold",
    [Lifetime Sales] >= 5000,  "Silver",
    [Lifetime Sales] >= 1000,  "Bronze",
    "Standard"
)

IF(test, then, [else]) returns blank if you omit the else part. SWITCH(expression, value1, result1, value2, result2, ..., [else]) compares one expression with a list of values. The pattern SWITCH(TRUE(), condition1, result1, condition2, result2, ..., else) is the DAX replacement for a chain of IFs: it returns the result for the first condition that is true, so put the strictest test first (as the tiers above do). Both functions are readable and efficient; nested IF more than two levels deep is a sign to switch to SWITCH(TRUE(), ...). The pattern also appears in DAX Patterns.

AND, OR, NOT, IN

DAX
Is Big Order = AND ( Sales[Qty] >= 10, Sales[Amount] >= 500 )
Is Big Order 2 = Sales[Qty] >= 10 && Sales[Amount] >= 500
Is Priority = Sales[Region] IN { "North", "East" }
Not Cancelled = NOT ( Sales[Status] = "Cancelled" )

AND and OR take only two arguments. For more, use the operators && (and) and || (or), or nest them. IN takes a table constructor in braces and is far tidier than Region = "North" || Region = "East".

Blank, zero and empty text

DAX has a special value BLANK: "no value". It shows up when a measure has no rows, when a relationship has no match, and when you write BLANK(). The rules are:

  • A visual usually hides rows where a measure is blank, which is why a measure that returns 0 instead of blank can add thousands of unwanted rows to a table.
  • In a comparison, BLANK() = 0 is TRUE and BLANK() = "" is TRUE. The = operator cannot tell them apart.
  • The strict equality operator == can: BLANK() == 0 is FALSE.
  • ISBLANK(x) is TRUE only for a real blank, not for 0 and not for "".
DAX
Has Sales = NOT ISBLANK ( [Total Sales] )
Sales or Zero = COALESCE ( [Total Sales], 0 )
Sales Text = IF ( ISBLANK ( [Total Sales] ), "no sales", FORMAT ( [Total Sales], "#,0" ) )

COALESCE(a, b, c, ...) returns the first argument that is not blank. It is shorter than IF(ISBLANK(...)) and is how you supply a default. Use COALESCE(measure, 0) sparingly: it makes a blank row appear as a zero, which fills visuals with empty categories.

Try it: blank, zero and COALESCE

Python has no BLANK, so this stand-in uses None for blank and builds the three comparisons by hand.

Python Stand-in: blank is not zero, even though = says so

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When the difference between "no sales" and "sales of 0" matters (for example a product that was sold and refunded), use ISBLANK or ==.

Errors: IFERROR, ISERROR, DIVIDE

DAX
Ratio Safe = IFERROR ( [Profit] / [Revenue], 0 )
Ratio Better = DIVIDE ( [Profit], [Revenue] )
Has Error = ISERROR ( [Profit] / [Revenue] )

IFERROR(value, alternative) catches any error and returns the alternative. It is convenient but hides real problems (a wrong column, a type mismatch), and it can slow a measure because the engine must evaluate both branches. For division, DIVIDE is better: it returns blank (or your alternative) for a zero denominator, and nothing else is swallowed.

Testing types

ISNUMBER, ISTEXT, ISNONTEXT and ISLOGICAL test the type of a value. They matter in columns that can hold more than one kind of value, such as a VALUE-converted field, and in measures reading a text column that is sometimes a number. In a typed model, columns have one type, so you mainly meet them while cleaning data that arrived as text, as in Data Types and Conversions in Power BI.

Information about the filter context

These functions look at what the user selected and are the core of measures that behave differently at different levels.

DAX
Selected Region =
IF ( HASONEVALUE ( Customers[Region] ), VALUES ( Customers[Region] ), "Multiple" )

Region Name =
SELECTEDVALUE ( Customers[Region], "All regions" )

Region Is Filtered = ISFILTERED ( Customers[Region] )

Level Label =
SWITCH (
    TRUE ( ),
    ISINSCOPE ( Product[Name] ),     "Product row",
    ISINSCOPE ( Product[Category] ), "Category row",
    "Grand total"
)
Function Question it answers
HASONEVALUE(col) Is exactly one value of this column visible in the current context?
SELECTEDVALUE(col, [alt]) The single visible value, or alt if there is not exactly one. Shorter than IF(HASONEVALUE(...), VALUES(...), alt).
ISFILTERED(col) Is there a direct filter on this column?
ISCROSSFILTERED(col) Is the column filtered directly or indirectly by another table?
ISINSCOPE(col) Is this column a grouping level of the current visual row? Perfect for matrix hierarchies.

ISINSCOPE is the right tool to detect the total row of a table or matrix: at the total row, no grouping column is in scope. HASONEVALUE is the older workaround and fails when a single category happens to be the only one left after a filter. The ranking lesson (Ranking, Top N and Percent of Total) uses ISINSCOPE to hide a total row and Row-Level Security uses USERPRINCIPALNAME.

Try it: a measure that depends on the level

Python Stand-in: ISINSCOPE, SELECTEDVALUE and SWITCH(TRUE())

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

customer_tier shows why the order of the conditions in SWITCH(TRUE(), ...) matters: 20,000 passes every test, and it becomes Gold only because Gold is tested first.

Looking up membership: CONTAINS, CONTAINSROW, ISEMPTY

DAX
Is VIP =
CONTAINS ( VIPCustomers, VIPCustomers[CustomerID], Customers[CustomerID] )

Is Weekend Day = CONTAINSROW ( { 6, 7 }, WEEKDAY ( 'Date'[Date], 2 ) )

No Rows = ISEMPTY ( FILTER ( Sales, Sales[Amount] > 1000 ) )

CONTAINS(table, column, value, ...) tests whether a table has a row with those values. CONTAINSROW tests whether a value (or row) is in a table, and is what powers IN. ISEMPTY(table) is true when a table expression returns no rows, and is faster than COUNTROWS(...) = 0. For a simple "is it in this list" test on a column, IN is usually the clearest.

USERNAME and USERPRINCIPALNAME

DAX
Current User = USERPRINCIPALNAME ( )
Greeting = "Hello, " & USERPRINCIPALNAME ( )

USERPRINCIPALNAME() returns the sign-in name of the person viewing, such as [email protected], in the service, and the Windows domain account in Desktop. USERNAME() returns DOMAIN\user on a domain and is not reliable in the cloud. For row-level security, always prefer USERPRINCIPALNAME. A measure that shows the user name is fun for debugging roles, but never use it to hide data in a visual, because hiding is not security: use RLS for that.

Practical advice

  • Prefer SWITCH(TRUE(), ...) over deeply nested IF.
  • Use DIVIDE, not IFERROR(a / b, 0).
  • Decide on purpose whether a missing value should be blank or zero, and test with ISBLANK when it matters.
  • Use ISINSCOPE for total rows, SELECTEDVALUE for single selections and ISFILTERED for "is the user filtering this?".
  • Remember that logic in a measure runs for every cell. If a condition depends only on the row, a calculated column may be cheaper.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In DAX, what does BLANK() = 0 return?

    The = operator treats BLANK as equal to 0 and to empty text. Use == or ISBLANK to tell them apart.
  2. 2. Which function returns the first argument that is not blank?

    COALESCE(a, b, c) walks through the arguments and returns the first one that is not blank.
  3. 3. How many arguments can the AND function take?

    AND and OR take two arguments. Use && and || to combine more conditions.
  4. 4. How do you detect the grand total row of a matrix whose rows are Product[Name]?

    At the total row the grouping column is not in scope. ISINSCOPE is true on the detail rows and false on the total.
  5. 5. Which function is recommended for the identity in row-level security rules in the cloud?

    USERPRINCIPALNAME returns the sign-in (UPN) in the service. USERNAME returns a domain format that is not reliable in the cloud.

Summary

  • IF decides between two outcomes; SWITCH(TRUE(), ...) replaces long IF chains and returns the first true branch.
  • AND and OR take two arguments, so use &&, || and IN for real conditions.
  • BLANK is not zero: = treats them as equal, == and ISBLANK do not; COALESCE supplies a default.
  • DIVIDE is better than IFERROR for ratios.
  • HASONEVALUE, SELECTEDVALUE, ISFILTERED and ISINSCOPE read the filter context and drive level-aware measures.
  • USERPRINCIPALNAME identifies the viewer, and is the basis of dynamic RLS.

Next: DAX Math and Statistical Functions.