DAX to get text value from related table, filtered by date

[brohon]

Hi all,

I'm having issue to solve one problem. I'm having DB of customer interactions with 2 tables with DB relationship:

Customer

Customer ID    |  Full name            

1                        Ned Stark

2                        Jamie Lannister           

3                        Jon Snow             

Transactions

Transaction ID    |  Customer ID   |   Date          | Salesman   

1                             1                       1.1.2016         Scot

2                             2                       3.5.2016         Joe

3                             1                      7.7.2017         Joe

4                             3                      7.7.2017         Mike

In the Customer table I'd like to add new filed containing the name of the Salesman from related table Transaction who made the first transaction with the customer. 

[v-huizhn-msft · Accepted solution]

Hi @brohon,

I reproduce your scenario and get expected result as follows.

1. Get data and created relationship shown in the screenshot below.



2. In 'Customer' table, create a calculated column using the formula.

Saleman =
LOOKUPVALUE (
    Transactions[Salesman],
    Transactions[Customer ID], Customer[Customer ID],
    Transactions[Date], CALCULATE (
        MIN ( Transactions[Date] ),
        RELATEDTABLE ( Transactions ),
        ALLEXCEPT ( Transactions, Transactions[Customer ID] )
    )
)

Please see the result displayed in below screenshot.



Best Regards,
Angelia

[brohon]

Wow Angelia, thank you very much for your help! Appreciate a lot!

Would you be so kind to help me even more? In my DB there are transactions that occurred the same day. Therefore the formula ends with "A table of multiple values was supplied where a single value was expected." I've tried to modify the MIN with TOPN but was not successfull.

[Zubair_Muhammad]

@brohon

Try adding another parameter (TransactionID) in @v-huizhn-msft formula

So if there are 2 salesman with same date for a customer, the one with lower transaction ID gets selected

Saleman =
LOOKUPVALUE (
    Transactions[Salesman],
    Transactions[Customer ID], Customer[Customer ID],
    Transactions[Date], CALCULATE (
        MIN ( Transactions[Date] ),
        RELATEDTABLE ( Transactions ),
        ALLEXCEPT ( Transactions, Transactions[Customer ID] )
    ),
    Transactions[TransactionID], CALCULATE (
        MIN ( Transactions[TransactionID] ),
        RELATEDTABLE ( Transactions ),
        ALLEXCEPT ( Transactions, Transactions[Customer ID] )
    )
)

[NatashaSchuster]

Hello @Zubair_Muhammad

Any chance you could please explain what happens under the hoop in the CALCULATE DAX portion, specifically how does RELATEDTABLE functions here ? 
The thing is that CALCULATE itself serves as a context transition, so it may seem it should be be here, but once it's taken away then we only one record (Scott) is returned int he Customer's table.  How does RELATEDTABLE() helps here to return all the records ? 

CALCULATE (

    MIN ( Transactions[Date] ),
    RELATEDTABLE ( Transactions ),  
     ALLEXCEPT ( Transactions, Transactions[CustomerID] )
)

Thank you

[brohon]

Thank you both very much for all your help!!!

[NatashaSchuster]

@v-huizhn-msft

Hi Angela, 

Great solution. In your CALCULATE logic, may we please know what role does RELATEDTABLE do? The thing is that CALCULATE itself serves as a context transition, so it may seem it should be be here, but once it's taken away then we only one record (Scott) is returned int he Customer's table.  How does RELATEDTABLE() helps here to return all the records ? 

CALCULATE (

    MIN ( Transactions[Date] ),
    RELATEDTABLE ( Transactions ),  
     ALLEXCEPT ( Transactions, Transactions[CustomerID] )
)

[laidback]

Need help...Getting previous value for Sales rep using a column that has month end date...Each month end has a value, could be the same or different. I just need to show the previous value for sales rep next to current month value running each month.....i have tryed using lookups...but did not work....all data is in the same table...Column name(s)...Sales Rep ID, Month End Date, Sales Rep Grade (ex. A,B,C,D,F).......also with a filter on 4th column Sales Rep Type = "New" for example....