Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

help regards to Lookup

Hi,   I've a query regards to Lookup: Presented a Sample data as below:   1. 've 2 Tables1 & Table2 2. Table1 has details of the Emp and dept he worked based on month. 3. Table2  has Email ID a...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    This calculated column formula in Table2 works

    =LOOKUPVALUE(Table1[Dept],Table1[Month],Table2[Month],Table1[Email ID],Table2[Email ID])

    Hope this helps.

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can also create calculated colmun use following formula if there are multi matched Dept Values in Table1

     

    Dept = 
VAR emp = [Emp]
VAR month = [Month]
VAR t =
    FILTER ( 'Table1', 'Table1'[Email Id] = emp && 'Table1'[Month] = month )
RETURN
    MAXX ( t, [Dept] )

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
6 years ago

This is in my file.

Max Sales order id = Maxx(filter(sales,Sales[item_id]='Item'[Item ID]),Sales[Order Id])

 

created a new one as Column not measure

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Could you pls attach .. I was creatig Column..

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can also create calculated colmun use following formula if there are multi matched Dept Values in Table1

     

    Dept = 
VAR emp = [Emp]
VAR month = [Month]
VAR t =
    FILTER ( 'Table1', 'Table1'[Email Id] = emp && 'Table1'[Month] = month )
RETURN
    MAXX ( t, [Dept] )

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks it works for me