Forum Discussion
juan_pablo
7 months agoHelper V
Filter a Matrix By Measure vs a Table
Hi, I have the following table [Table] with just one Order ID that contains three items of type A or B: I created the measure "Order With B" to check if orders contain type B items. The m...
- 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:
lbendlin
7 months agoSuper User
Your measure filter cannot be part of the visual it is filtering.