How to filter parent and all child rows in matrix table using status of child rows

[Anonymous]

Hello,

I have this data and I visualized it using a matrix table. 

Transaction	Invoice	Status
NO-11297	NO-11297-1	Void
NO-11297	NO-11297-2	Invoice Paid
NO-11297	NO-11297-3	Invoice Sent
NO-11301	NO-11301-1	Invoice Paid
NO-11301	NO-11301-2	Invoice Paid
NO-11303	NO-11303-1	Invoice Paid
NO-11303	NO-11303-2	Invoice Paid
NO-11305	NO-11305-1	Invoice Paid
NO-11305	NO-11305-2	Invoice Paid
NO-11308	NO-11308-1	Invoice Paid
NO-11308	NO-11308-2	Invoice Sent
NO-11309	NO-11309-1	Invoice Paid
NO-11309	NO-11309-2	Invoice Sent

I am trying to filter the table to show parent transactions + all their child rows if subtotal of COLUMN > 0

Column = IF(data2[Status] = "Invoice Paid", 0, 1)

--> hide all in yellow highlight

When trying to use the COLUMN as filter, it only filters those with Invoice Sent or Void values. I need to see all children of these parent transactions

Thank you in advance!

[Jos_Woolley]

You're welcome!

Cheers

[Jos_Woolley · Accepted solution]

Hi,

Use a measure instead:

MyMeasure1 =
CALCULATE(
    COUNTROWS( data2 ),
    data2[Status] <> "Invoice Paid",
    ALLEXCEPT(
        data2,
        data2[Transaction]
    )
)

Drag it into the filters pane and set it to greater than 0.

Regards

[Anonymous]

Excellent! Thank you so much, i really appreciate it!