help regards to Lookup

[Anonymous]

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 and Month need to get the Dept names based on month. 

Please help with Dax

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula in Table2 works

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

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula in Table2 works

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

Hope this helps.

[amitchandak]

Try to create department as a column in table 2

dep id = Maxx(filter(table1,table1[month]='table2'[month] && table1[email id ]='table2'[emp]),table1[dept]) 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi Amith,

This doest nork for me, also the Syntax for Maxx is Maxx(Table,Expression). Could you pls attach a Sample and help me

[Anonymous]

Sorry, Dint work for me

[amitchandak]

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]

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

[v-lid-msft · Accepted solution]

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]

Thanks it works for me