cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brohon
Frequent Visitor

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

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. 

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft
Microsoft

Hi @brohon,

I reproduce your scenario and get expected result as follows.

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

1.PNG

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.

1.PNG

Best Regards,
Angelia

View solution in original post

7 REPLIES 7
v-huizhn-msft
Microsoft
Microsoft

Hi @brohon,

I reproduce your scenario and get expected result as follows.

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

1.PNG

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.

1.PNG

Best Regards,
Angelia

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.

@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] )
    )
)

Regards
Zubair

Please try my custom visuals

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

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

@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] )
)

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....

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors