Looking for a way to return the first value based on two sorting functions

[AdiKujan]

Hi guys,

I have a table which party looks as follows:

Customer,    Order#,    OrderDate,    SalesChannel

We can have multiple customers, with multiple OrderDates, and SalesChannels, but there can only be one Order#.

I need a 5th column which returns to me the first SalesChanel based on Orderdate and Order#

It needs to look at the OrderDate first, and Order# second. The reason for this is we have used multiple Order# formats over time.

Hopefully one of you guys can help me with this problem. If it's not clear I can post some screenshots.

[Anonymous · Accepted solution]

HI @AdiKujan,

You can try to use below formula if it works on your side:

First SalesChannel =
VAR min_date =
    CALCULATE (
        MIN ( Table[OrderDate] ),
        FILTER (
            ALL ( Table ),
            [Customer] = EARLIER ( Table[Customer] )
                && [Order#] = EARLIER ( Table[Order#] )
        )
    )
RETURN
    CALCULATE (
        MIN ( Table[SalesChannel] ),
        FILTER (
            ALL ( Table ),
            [Customer] = EARLIER ( Table[Customer] )
                && [Order#] = EARLIER ( Table[Order#] )
                && [OrderDate] = min_date
        )
    )

If above not help, can you please share some sample data for test and coding formula?

Regards,

Xiaoxin Sheng