Dinamic dax with filters

[msam86]

I have a table called Consolidated, where I combined 3 tables (2024, 2025, 2026):

Consolidado =
UNION(
SELECTCOLUMNS(
'2024',
"Product", '2024'[Product],
"Supplier", '2024'[Supplier],
"Supported by", '2024'[Supported by],
),
SELECTCOLUMNS(
'2025',
"Product", '2025'[Product],
"Supplier", '2025'[Supplier],
"Supported by", '2025'[Supported by],
),
SELECTCOLUMNS(
'2026',
"Product", '2026'[Product],
"Supplier", '2026'[Supplier],
"Supported by", '2026'[Supported by],
),
)
)

I have a parameter called ParameterData with the following values:

DAX
ParameterData = GENERATESERIES(2023, 2026, 1)

In each Year worksheet (2024, 2025, 2026), the "Supported by" field varies. For example:

2024
Product | Supplier | Supported by
Mobile Phone | Samsung | Samsung
Laptop | Dell | Dell
Tablet | Samsung | Samsung

2025
Product | Supplier | Supported by
Mobile Phone | Samsung | Tech Store
Laptop | Dell | Dell
Tablet | Samsung | Samsung

2026
Product | Supplier | Supported by
Mobile Phone | Samsung | Tech Store
Laptop | Dell | Tech Store
Tablet | Samsung | Samsung

I created a matrix and inserted the three fields. I also created a slicer and set the field to "Supported by." When I try to filter with the following criteria (ParameterData = 2026, Supported by = Tech Store), it filters but still shows the tablet, but with an empty "SupportedBy" field:

Product | Supplier | Supported by
Cell phone | Samsung | Tech Store
Notebook | Dell | Tech Store
Tablet | Samsung |

I would like it to show only what was selected in the slicer:

Product | Supplier | Supported by
Cell phone | Samsung | Tech Store
Notebook | Dell | Tech Store

How can I do this?

[msam86]

Hello  johnbasha33, 

Sorry for the delay. I saw your reply, you gave me ideas and helped me find the final solution for my original code. Thank you so much for your help.

[johnbasha33 · Accepted solution]

Certainly! Here's a clean and friendly version of your reply, tailored for posting in a Microsoft Fabric or Power BI Community Forum:

---

Hi there! You're running into a common behavior in Power BI when using slicers and matrix visuals on combined tables like yours.

🧩 Why You're Seeing "Tablet" with a Blank "Supported by"

Even though you're filtering Supported by = Tech Store and Year = 2026, the matrix still shows "Tablet" because Power BI keeps all rows from the data unless explicitly filtered out — even if one of the values (like “Supported by”) becomes blank after slicer selection.

---

✅ How to Fix It

You can solve this by adding a filter that hides rows where "Supported by" becomes blank after slicer filtering.

---

🔹 Option 1: Use a Visual-Level Filter with a Measure

Create a measure like this:

Show Rows =
IF(
    ISFILTERED('ParameterData'[Value]),
    IF(
        NOT ISBLANK(SELECTEDVALUE('Consolidado'[Supported by])),
        1,
        0
    ),
    1
)

Then apply this Show Rows measure as a visual-level filter to your matrix, and set it to show only when the value = 1.

This will hide rows like "Tablet" where the filtered value doesn’t match and results in blank.

---

🔹 Option 2: Build a Filtered Table

If you prefer, you can create a filtered version of your union table:

FilteredConsolidado =
FILTER (
    ADDCOLUMNS (
        UNION (
            SELECTCOLUMNS('2024', "Year", 2024, "Product", '2024'[Product], "Supplier", '2024'[Supplier], "Supported by", '2024'[Supported by]),
            SELECTCOLUMNS('2025', "Year", 2025, "Product", '2025'[Product], "Supplier", '2025'[Supplier], "Supported by", '2025'[Supported by]),
            SELECTCOLUMNS('2026', "Year", 2026, "Product", '2026'[Product], "Supplier", '2026'[Supplier], "Supported by", '2026'[Supported by])
        ),
        "YearFilter", SELECTEDVALUE(ParameterData[Value])
    ),
    [Year] = [YearFilter]
        && NOT(ISBLANK([Supported by]))
)

Then use FilteredConsolidado in your matrix visual instead of the original Consolidado.

---

🔧 Bonus Tip

If you haven’t already, add a Year column when building the union to make filtering easier and avoid relying solely on the slicer context.

---

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!