Forum Discussion

juan_pablo's avatar
juan_pablo
Helper V
7 months ago
Solved

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...
  • OwenAuger's avatar
    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:

    1. For the Matrix visual:
      1. The [Orders With B] measure is evaluated for all existing combinations of Order ID and Item ID (the two Row fields) even though Item ID is not expanded.
      2. This result is then limited to combinations where [Orders With B] is nonblank (default behaviour of SUMMARIZECOLUMNS).
      3. Finally the [Orders With B] > 0 filter is applied.
    2. For the Table visual:
      1. The [Orders With B] measure is evaluated for each Order ID.
      2. This result is then limited to Order ID values where [Orders With B] is nonblank (default behaviour of SUMMARIZECOLUMNS).
      3. Finally the [Orders With B] > 0 filter is applied.

    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