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

cengizhanarslan's avatar
cengizhanarslan
Super User
7 months ago

If you want to filter orders based on “contains B”, the most robust pattern is to create an order-level flag (or a measure that evaluates only at Order ID granularity using ISINSCOPE / REMOVEFILTERS(Item ID)), but the behavior you observed is explained by the Matrix query plan Power BI generates. The fix that actually solves the Matrix behavior you need an Order-level measure that removes the child row context:

 

Has B (Order Level) =
VAR c =
    CALCULATE(
        COUNTROWS('Table'),
        REMOVEFILTERS('Table'[Item ID]),        -- remove the lower level
        'Table'[Item Type] = "B"
    )
RETURN
IF(c > 0, 1, BLANK())

 

Then:

  • Put this measure in Visual level filters for the Matrix

  • Filter to is not blank (or equals 1)

Now the filter is evaluated at Order ID level, so the Matrix will behave like the Table even when Item ID is present but collapsed.