How Returning BLANK() in a Measure Fixes Filtering Issues in Tables

on

How Returning BLANK() in a Measure Fixes Filtering Issues in Tables

Today I ran into an interesting Power BI behavior that caused a table visual to ignore slicer filters — but only when a certain measure was included. This turned into a great learning that I’m documenting here for others who may face the same issue.

🔍 The Problem

I had a table showing Supplier‑level metrics, and everything filtered correctly when I used the slicer on SupplierCode.

But when I added the following measure:

Bonus Scores =
IF([% Ppk>1.33] >= 0.8, 1, 0)

…the table suddenly showed all suppliers, even if the slicer was set to display just one.

At first glance this was confusing because the slicer worked fine without the measure.

🧠 Why This Happens

When a measure returns 0 instead of BLANK(), Power BI interprets this as “there is data for this row.”

So even if a Supplier has no rows in the fact table, the measure forces the visual to display that Supplier because the table sees a valid numeric value.

This behavior, combined with the visual’s internal logic, often causes:

  • Phantom rows
  • Rows that don’t match slicer selections
  • Rows without underlying fact data

✅ The Fix: Return BLANK() for Missing Data

To fix the issue, I updated my measure like this:

Bonus Scores =
IF(
    ISBLANK([% Ppk>1.33]),
    BLANK(),
    IF([% Ppk>1.33] >= 0.8, 1, 0)
)

✔ Why this works

  • If the base measure ([% Ppk>1.33]) has no data, the measure outputs BLANK().
  • In Power BI, BLANK rows do not force visuals to show up.
  • This lets the slicer behave normally, and the table now displays only the relevant Suppliers.

The moment I switched to returning BLANK(), the Supplier slicer started working perfectly again.

💡 Key Takeaway

Always return BLANK() for rows that don’t have underlying fact data.

This prevents visuals from rendering unwanted rows and ensures slicers work correctly.

This is a best-practice pattern for any measure that controls row visibility.

🏁 Final Thoughts

This was a simple but powerful reminder of how Power BI handles BLANK vs. numeric values in visuals. If you ever find a visual not responding to slicers or showing unexpected rows, check whether your measure is unintentionally returning a zero instead of a BLANK.

A tiny DAX change can completely fix the experience.

Comments

Post a Comment