🧠

BLANK

How DAX Treats BLANK

DAX has a special value called BLANK that behaves differently depending on the context. Understanding BLANK is crucial for accurate calculations in Power BI.

What is BLANK?

BLANK is DAX's way of representing missing, empty, or null values, Absence of a value
It's NOT the same as zero (0), empty string (""), or NULL
BLANK is returned by the BLANK() function or when a value doesn't exist
What happens to BLANK values in arithmetic operations?…) They are treated as zero

How DAX Treats BLANK in Different Contexts

1. Arithmetic Operations (Math)

BLANK is treated as zero (0) in mathematical operations:

// BLANK behaves like 0 in math
Result = 10 + BLANK()
// Returns: 10 (because 10 + 0 = 10)

Result = 5 * BLANK()
// Returns: 0 (because 5 * 0 = 0)

Result = BLANK() / 5
// Returns: BLANK (0 / 5 = 0, but DAX returns BLANK)

2. Text/String Operations

BLANK is treated as an empty string (""):

// BLANK behaves like empty string
FullName = "John" & BLANK()
// Returns: "John" (concatenates with empty string)

FullName = BLANK() & " Smith"
// Returns: " Smith"

3. Logical Operations (TRUE/FALSE)

BLANK is treated as FALSE in Boolean contexts:

// BLANK behaves like FALSE
IsActive = BLANK() || TRUE
// Returns: TRUE (because FALSE OR TRUE = TRUE)

IsActive = BLANK() && TRUE
// Returns: FALSE (because FALSE AND TRUE = FALSE)

4. Comparison Operations

BLANK has special behavior in comparisons:

// BLANK in comparisons
Result = BLANK() = 0       // Returns: TRUE (BLANK equals 0)
Result = BLANK() = ""      // Returns: TRUE (BLANK equals empty string)
Result = BLANK() = FALSE   // Returns: TRUE (BLANK equals FALSE)

Result = BLANK() < 5       // Returns: TRUE (BLANK is less than any number)
Result = BLANK() > -5      // Returns: FALSE

Key DAX Functions for BLANK

1. BLANK() - Create a BLANK value

Measure =
IF(
    [Total Sales] = 0,
    BLANK(),  // Return BLANK instead of 0
    [Total Sales]
)

2. ISBLANK() - Check if value is BLANK

HasSales =
IF(
    ISBLANK([Total Sales]),
    "No Sales",
    "Has Sales"
)

3. COALESCE() - Replace BLANK with another value

// Returns first non-BLANK value
SafeValue = COALESCE([Sales], [Budget], 0)
// If Sales is BLANK, try Budget; if Budget is BLANK, use 0

4. IF() with BLANK handling

CleanValue =
IF(
    ISBLANK([Value]),
    0,          // Replace BLANK with 0
    [Value]
)

BLANK vs Zero vs Empty String

Context	BLANK	0	"" (Empty String)
Math	Treated as 0	Is 0	Error
Text	Treated as ""	Displays "0"	Is empty
Logical	Treated as FALSE	TRUE (non-zero)	TRUE (exists)
Visual Display	Shows as empty	Shows "0"	Shows empty
COUNT()	Not counted	Counted	Counted
COUNTROWS()	Counted	Counted	Counted

Common Scenarios

Scenario 1: Avoiding Division by Zero

Profit Margin =
DIVIDE(
    [Profit],
    [Sales],
    BLANK()  // Return BLANK if Sales = 0, instead of error
)

Explanation:

Line 1: Creates a measure called "Profit Margin"
Line 2: Uses DIVIDE function (safe division)
Line 3: First argument is Profit (numerator)
Line 4: Second argument is Sales (denominator)
Line 5: Third argument specifies what to return if division by zero occurs (BLANK instead of error)

Scenario 2: Removing Zeros from Visuals

Sales Clean =
IF(
    [Total Sales] = 0,
    BLANK(),        // Return BLANK to hide from chart
    [Total Sales]
)

Explanation:

Line 2: Check if Total Sales equals zero
Line 3: If true, return BLANK (won't display in visual)
Line 4: If false, return the actual Total Sales value

Scenario 3: Handling Missing Data

Commission =
VAR SalesAmount = [Total Sales]
RETURN
    IF(
        ISBLANK(SalesAmount),
        0,                          // No sales = no commission
        SalesAmount * 0.05          // 5% commission
    )

Explanation:

Line 2: Store Total Sales in a variable
Line 3: RETURN starts the calculation logic
Line 5: Check if SalesAmount is BLANK
Line 6: If BLANK, return 0 (no commission for no sales)
Line 7: If not BLANK, calculate 5% commission

Aggregation Functions and BLANK

SUM() - Ignores BLANK

Total = SUM(Sales[Amount])
// BLANKs are ignored, only numbers are summed

COUNT() vs COUNTROWS()

// COUNT - Counts non-BLANK values only
Count Values = COUNT(Sales[Amount])
// Returns: 5 (if 5 rows have values)

// COUNTROWS - Counts all rows (including BLANK)
Count Rows = COUNTROWS(Sales)
// Returns: 10 (if table has 10 rows total)

AVERAGE() - Ignores BLANK

Avg Sales = AVERAGE(Sales[Amount])
// BLANKs are excluded from average calculation

Best Practices

1. Use BLANK() for Clean Visuals

// Good: Returns BLANK when no data
YTD Sales =
IF(
    HASONEVALUE(Calendar[Year]),
    [Sales],
    BLANK()  // Don't show misleading totals
)

2. Handle BLANK Before Calculations

// Good: Check for BLANK first
Growth Rate =
VAR CurrentYear = [Sales Current Year]
VAR PriorYear = [Sales Prior Year]
RETURN
    IF(
        ISBLANK(PriorYear) || PriorYear = 0,
        BLANK(),
        DIVIDE(CurrentYear - PriorYear, PriorYear)
    )

3. Use COALESCE for Default Values

// Replace multiple BLANKs with a default
Display Value = COALESCE([Actual], [Forecast], [Budget], 0)

Important DAX Behaviors to Remember

BLANK propagates in calculations: If any part of a calculation is BLANK, the result may be BLANK
BLANK is contagious: 10 + BLANK() returns 10, but BLANK() & "text" returns "text"
Filters treat BLANK specially: BLANK values may be included or excluded depending on filter context
Visual display: BLANK appears as empty space in tables but may show as "(Blank)" in slicers

Quick Reference Table

Operation	BLANK Behavior	Example	Result
Addition	Treated as 0	`5 + BLANK()`	5
Multiplication	Treated as 0	`5 * BLANK()`	0
Concatenation	Treated as ""	`"Hi" & BLANK()`	"Hi"
Comparison	Special rules	`BLANK() = 0`	TRUE
IF condition	Treated as FALSE	`IF(BLANK(), "Yes", "No")`	"No"
SUM()	Ignored	`SUM({1,2,BLANK()})`	3

Understanding BLANK is essential for the PL-300 exam and for writing robust DAX measures in Power BI. Always test your measures with BLANK values to ensure they behave as expected!