Filtering Matrix does not work

[kathrin_44]

Hello,

I have a problem when i Want to filter my Matrix.

This is the matrix i have:

The column "__days left" is  a measure: __days left =

var __all sub = CALCULATE (SELECTEDVALUE(Tabelle22[Days left]), FILTER(Tabelle22, NOT(ISBLANK(Tabelle22[Level3]))))

var all tasks = CALCULATE (SUM(Tabelle22[Days left]), FILTER(Tabelle22, ISBLANK(Tabelle22[Level3])), REMOVEFILTERS (Tabelle22[Level3]))

return  IF(ISINSCOPE(Tabelle22[Level3]), [__all sub], [__all tasks])

I want to filter by days left. using the originak days left column does not filter the matrix right when the Tasks are closed. It seems that the filter cannot reach this Level when the hierachynis closed. But how do I filter by days left? can i filter by the measure??

I am so confused and thankful for any tips!

Thank you!

[freginier]

Hi @kathrin_44,

Thank you for sharing your question! This is a known limitation when filtering a matrix visual by a DAX measure, especially one that uses ISINSCOPE. When rows are collapsed in a hierarchy, Power BI evaluates the measure at the parent level, which can cause unexpected behavior with filters.

Here are a few things you can try:

1. Use a visual-level filter on the measure: In the Filters pane, drag the __days left measure to the "Filters on this visual" section and set your condition. Note that this filters at the row level that is currently visible, not at sub-task level when collapsed.

2. Add a calculated column instead: If you need to filter on a fixed numeric value, consider creating a calculated column in your table and use that as your filter. Calculated columns are evaluated row by row at import time and filter more reliably.

3. Use CALCULATE with REMOVEFILTERS: If you want sub-tasks to be visible based on their days-left value even when collapsed, consider restructuring your measure to propagate the filter context down to the lowest level.

Could you clarify what filtering behavior exactly you expect — for example, should rows disappear from the matrix when days left = 0, or should they simply show 0?

Hope this helps!

[freginier]

Hi @kathrin_44,

Thank you for sharing your question! This is a known limitation when filtering a matrix visual by a DAX measure, especially one that uses ISINSCOPE. When rows are collapsed in a hierarchy, Power BI evaluates the measure at the parent level, which can cause unexpected behavior with filters.

Here are a few things you can try:

1. Use a visual-level filter on the measure: In the Filters pane, drag the __days left measure to the "Filters on this visual" section and set your condition. Note that this filters at the row level that is currently visible, not at sub-task level when collapsed.

2. Add a calculated column instead: If you need to filter on a fixed numeric value, consider creating a calculated column in your table and use that as your filter. Calculated columns are evaluated row by row at import time and filter more reliably.

3. Use CALCULATE with REMOVEFILTERS: If you want sub-tasks to be visible based on their days-left value even when collapsed, consider restructuring your measure to propagate the filter context down to the lowest level.

Could you clarify what filtering behavior exactly you expect — for example, should rows disappear from the matrix when days left = 0, or should they simply show 0?

Hope this helps!