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 measure returns 0 if the order contains type B items, and 0 otherwise:

Orders With B = 
ISBLANK(
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[Item Type] IN {"B"}
    )
)+0

Then I created a table with Order ID and the measure, and a matrix with Order ID, Item ID (collapsed) and the measure:

 

As expected, both visuals return the same result. 

Now I want to filter both visuals by the measure, filtering out the orders with type B items, this is:

 

after applying this filter to the matrix, suddenly the measure now returns a 1 and keeps the order in the visual as opposed to the table that removed the order as expected:

If I remove the collapsed column "Item ID", the order disappear.
How is it possible that the collapsed columns change the filter context of the measure in the parent where Item ID is not in the scope? 

It was supposed that collapsed matrix should behave the same way as the table. 

 

Can someone explain this to me? (I don't want a work around, I want to understand what is happening)

 

Thank you!

 

 

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

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    7 months ago

    Your measure filter cannot be part of the visual it is filtering.

  • OwenAuger's avatar
    OwenAuger
    Super User
    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

    • juan_pablo's avatar
      juan_pablo
      Helper V
      7 months ago

      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.

  • 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.