Forum Discussion

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

Please help with Dax

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

8 Replies

amitchandak
Super User
6 years ago
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
  Not applicable
  6 years ago
  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
    Not applicable
    6 years ago
    Sorry, Dint work for me
Ashish_Mathur
Super User
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.