Forum Discussion

khisla's avatar
khisla
Helper II
1 year ago
Solved

X Lookup within same table

Hello Community I have the below data columns withiin my table and need to get the below result (i.e. original receipt date column). I need to x-lookup the retrieved from receipt no. within the rec...
  • Fowmy's avatar
    Fowmy
    1 year ago

    khisla 
    Add the following Calculated Column to your table, rename the table as per your model":

    Orignal Receipt Date = 

VAR __Rno = Table02[Retrieved from Receipt No.]
VAR __Date = MAXX( FILTER( Table02 , Table02[Receipt No.] = __Rno ) , Table02[Date] )
RETURN
    __Date

     

     

     

quantumudit's avatar
quantumudit
Super User
1 year ago

Hello khisla 

I hope I have correctly understood your query and appreciate the provided sample dataset.

 

Below is the DAX formula to create a calculated column that performs a lookup within the same table to retrieve the corresponding date based on the given receipt number.

 

Orignal Receipt Date - Calculated = LOOKUPVALUE('Table'[Date],'Table'[Receipt No.],'Table'[Retrieved from Receipt No.])

 

Here is the screenshot where you can review the "Expected Original Receipt Date" column and the one made with the DAX calculation, so that you can easily compare the results with the expected ones.

 

 

I am also attaching the Power BI file for your reference. Please let me know if you need any further information.

 

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

 

Proud to be a SuperUser

 

 

 

 

 

 

quantumudit's avatar
quantumudit
Super User
1 year ago

Hello khisla

I would appreciate your feedback on whether the DAX formula I provided as part of the solution to your query is functioning correctly. Please inform me if it is not working.

 

Thanks, 

Udit