Forum Discussion

AdiKujan's avatar
AdiKujan
Frequent Visitor
7 years ago
Solved

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

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.

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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