Nested Filter

Hi Anonymous,

Based on my test, you can firstly use the formula below to create a new calculate column in your table to indicate if a customer has order instance as 5.

HasFive = 
IF (
    COUNTROWS (
        FILTER (
            ALL ( Table1 ),
            Table1[Customer] = EARLIER ( Table1[Customer] )
                && Table1[Order Instance] = 5
        )
    )
        >= 1,
    1,
    0
)

Then you should be able to use the formula below to create a measure to calculate sum of the revenue of those customers only but revenue should be summed for all the order instance till 5.

Measure = 
CALCULATE (
    SUM ( Table1[revenue] ),
    FILTER ( Table1, Table1[HasFive] = 1 && Table1[Order Instance] <= 5 )
)

Here is the sample pbix file for your reference. :smileyhappy:

Regards

it worked with bit work around, thank you