Filter on the results generated by another filter

[finn881988]

I have a Monthly Table has such info: customerID, transactionTimeStamp, OrderStatus, OrderType, etc.

customerID	transactionTimeStamp	OrderStatus	OrderType
111111	2022-12-12	TH	SK
111111	2022-12-12	P	SK
111111	2022-12-12	X	SK
111111	2022-12-13	P	AU
222222	....

I'd like to get:

Step1: all customers who placed AU order and has OrderStatus = P.

Step2: use the customer list to find the very LAST SK type order with status in TH among all these customers extracted from step1.

The output should be one TIMESTAMP.

Any help is appreciated!

(I tried to use two layers filter function but ended up with error saying: A single value for column cannont be determmined. This can happen when a measure formula refers to a column that contains many values.) 

[v-zhangti · Accepted solution]

Hi, @finn881988 

You can try the following methods.

Measure = 
VAR _table =
    FILTER (
        SUMMARIZE ('Table',
            'Table'[transactionTimeStamp],'Table'[OrderStatus],'Table'[OrderType],
            "customerID",
                CALCULATE ( MAX ( 'Table'[customerID] ),
                    FILTER ( ALL ( 'Table' ),
                        [OrderType] = "AU" && [OrderStatus] = "P"
                            && [customerID] = SELECTEDVALUE ( 'Table'[customerID] ) ) ) ),
        [customerID] <> BLANK ()
    )
RETURN
    MAXX ( FILTER ( _table, [OrderType] = "SK" && [OrderStatus] = "TH" ),
        [transactionTimeStamp]
    )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @finn881988 

try to write a measure like this:

TimeStampe = 
VAR _customerlist =
CALCULATETABLE(
    VALUES(TableName[customerID]),
    FILTER(
        TableName,
        TableName[OrderStatus]="P"
            &&TableName[OrderType]="AU"
    )
)
VAR _table =
FILTER(
    TableName,
    TableName[customerID] IN _customerlist
)
RETURN
MAXX(
    FILTER(
       _table,
            TableName[OrderStatus]="TH"
            &&TableName[OrderType] = "SK"
    ),
    TableName[TransactionTimeStamp]
)

i tried with an expended dataset and it worked like this: