Reverse Xlookup in PowerBI

[MR3110]

Hi , I need an assitance over the functions similar to Reverse Xlookup.

Two tables given and I want Rollno in table2 as per latest Date pick the roll no for the corresponding email. I need to add this function as calculated columns. 

Table-1

Date	Email	RollNO
12-04-2023	priyanka.aura02@gmail.com	23FMG01071
13-04-2023	vinrajhealth@gmail.com	23FMG03093
13-04-2023	dusarivasavi@gmail.com	23FMG02334
14-04-2023	kalyani.pedapatnapu@gmail.com	23 FMG02183
14-04-2023	patukurirajeswari.2@gmail.com	23FMG 04059
19-04-2023	sunandachowdary09@gmail.com	23FMG03152
17-04-2023	priyanka.aura02@gmail.com	23FMG04221
17-04-2023	vinrajhealth@gmail.com	23FMG04120
17-04-2023	priyanka.aura02@gmail.com	22FMG10082
17-04-2023	priyanka.aura02@gmail.com	23FMG01315
17-04-2023	vinrajhealth@gmail.com	23FMG03226
17-04-2023	dusarivasavi@gmail.com	22FMG12561

Table-2

Email	RollNo
priyanka.aura02@gmail.com	23FMG01315
vinrajhealth@gmail.com
dusarivasavi@gmail.com
kalyani.pedapatnapu@gmail.com
patukurirajeswari.2@gmail.com
sunandachowdary09@gmail.com

[mark_endicott · Accepted solution]

@MR3110 - You have 3 different RollNo's for priyanka.aura02@gmail.com on the same date, so it is unclear what the criteria for choosing between them is. However, looking at your results, I'm going to assume you would like the last value for that date, in which case you need to add an Index column to Table1 in Power Query. 

Transform Data >> Add Column >>Index Column >> From 1

After this your table will look like this:

Hit "close and apply", then you can add a calculated column to Table2 with the DAX below:

RollNo = 
VAR _email = Table2[Email]
VAR max_date = CALCULATE( MAX( Table1[Date] ), Table1[Email] = _email)
VAR max_index = CALCULATE( MAX( Table1[Index] ), Table1[Email] = _email)

RETURN
CALCULATE( MAX( Table1[RollNO] ), Table1[Date] = max_date && Table1[Index] = max_index )

Evidence this works: 

If this works for you, please accept as the solution so others can find it. 

[MR3110]

Thank You, it works well

[Mohamed23]

To achieve this in Excel, you can use a combination of the XLOOKUP and MAXIFS functions, or use FILTER in combination with INDEX and MATCH. Since you want to find the RollNo based on the latest date for each email in Table-2, here's a step-by-step guide:

Steps:

1. Create a helper column in Table-1:

   • Create a new column in Table-1 to combine Email and Date.
   • Example formula: =A2 & "|" & B2 (assuming Date is in column A and Email is in column B).

   • Find the latest Date for each Email:

     • In Table-2, use the MAXIFS function to find the latest date for each email.
     • Example formula: =MAXIFS(Table1[Date], Table1[Email], A2) (assuming Email in Table-2 is in column A).

     • Use XLOOKUP to find the RollNo for the latest Date:

       • Now, use the XLOOKUP function to get the RollNo based on the latest Date and Email combination.
       • Example formula: =XLOOKUP(MAXIFS(Table1[Date], Table1[Email], A2), Table1[Date], Table1[RollNo]).

       • Apply the formula to Table-2:

         • Drag down the formula in Table-2 to fill in the RollNo for each email.

           Example Formula for Table-2:

           Assuming your columns are as follows:

           • Table-1: Date (A), Email (B), RollNo (C)
           • Table-2: Email (A), RollNo (B)

             You can use this formula in Table-2, Column B (RollNo):

             excel

             Copy code

             =INDEX(Table1[RollNo], MATCH(1, (Table1[Email]=A2) * (Table1[Date]=MAXIFS(Table1[Date], Table1[Email], A2)), 0))

             Explanation:

             • MAXIFS(Table1[Date], Table1[Email], A2): Finds the latest date for the email in A2.
             • MATCH(...): Matches the combination of the latest date and the email to find the correct row.
             • INDEX(Table1[RollNo], ...): Fetches the RollNo corresponding to the matched row.

               Important:

               • Make sure to press Ctrl+Shift+Enter after typing the formula if you are using an older version of Excel that requires array formulas.

                 This method will give you the latest RollNo for each email in Table-2 based on the most recent date from Table-1.