Forum Discussion
Filter a Matrix By Measure vs a Table
- 7 months ago
Hi juan_pablo
To understand what is happening, examine the DAX queries generated for each visual in DAX query view (see here).
The short explanation is:
- For the Matrix visual:
- The
[Orders With B]measure is evaluated for all existing combinations ofOrder IDandItem ID(the two Row fields) even thoughItem IDis not expanded. - This result is then limited to combinations where
[Orders With B]is nonblank (default behaviour ofSUMMARIZECOLUMNS). - Finally the
[Orders With B] > 0filter is applied.
- The
- For the Table visual:
- The
[Orders With B]measure is evaluated for eachOrder ID. - This result is then limited to
Order IDvalues where[Orders With B]is nonblank (default behaviour ofSUMMARIZECOLUMNS). - Finally the
[Orders With B] > 0filter is applied.
- The
The expressions representing the visual-level filters within the DAX queries are shown below (edited for formatting):
1. Matrix:
VAR __ValueFilterDM3 = FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'Table'[Order ID], 'Table'[Item ID], "Orders_With_B", [Orders With B] ) ), [Orders_With_B] > 0 )2. Table:
VAR __ValueFilterDM1 = FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'Table'[Order ID], "Orders_With_B", [Orders With B] ) ), [Orders_With_B] > 0 )So for the Matrix, since at least one
Item Typeother than B exists forOrder ID1,Order ID1 is still visible in the visual when the filter is applied.I'll leave the question of whether this behaviour is "intuitive" and how to achieve your intended result to a separate discussion.
Regards
- For the Matrix visual:
Hi juan_pablo
To understand what is happening, examine the DAX queries generated for each visual in DAX query view (see here).
The short explanation is:
- For the Matrix visual:
- The
[Orders With B]measure is evaluated for all existing combinations ofOrder IDandItem ID(the two Row fields) even thoughItem IDis not expanded. - This result is then limited to combinations where
[Orders With B]is nonblank (default behaviour ofSUMMARIZECOLUMNS). - Finally the
[Orders With B] > 0filter is applied.
- The
- For the Table visual:
- The
[Orders With B]measure is evaluated for eachOrder ID. - This result is then limited to
Order IDvalues where[Orders With B]is nonblank (default behaviour ofSUMMARIZECOLUMNS). - Finally the
[Orders With B] > 0filter is applied.
- The
The expressions representing the visual-level filters within the DAX queries are shown below (edited for formatting):
1. Matrix:
VAR __ValueFilterDM3 =
FILTER (
KEEPFILTERS (
SUMMARIZECOLUMNS (
'Table'[Order ID],
'Table'[Item ID],
"Orders_With_B", [Orders With B]
)
),
[Orders_With_B] > 0
)
2. Table:
VAR __ValueFilterDM1 =
FILTER (
KEEPFILTERS (
SUMMARIZECOLUMNS (
'Table'[Order ID],
"Orders_With_B", [Orders With B]
)
),
[Orders_With_B] > 0
)
So for the Matrix, since at least one Item Type other than B exists for Order ID 1, Order ID 1 is still visible in the visual when the filter is applied.
I'll leave the question of whether this behaviour is "intuitive" and how to achieve your intended result to a separate discussion.
Regards
Thank you for the detailed explanation, that was exactly what I wanted to understand.
Definitely is not an intuitive behaviour, or at least Power BI should warn users someway that this can happen when using filters on matrix visuals.
Collapsed rows (columns) disappear from the filter context when evaluating measures, also when crossfiltering with the matrix, so this behaviour should be consistent along the report, not do an exception in the filter pane.
Thank you again.