Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.