LookupValue with Filter

[nagaraj]

Hi,

 Need help with DAX for below scenario

Scenario: Have TABLE1 and TABLE2, need to populate manager (in TABLE1) by having lookup (in TABLE2).

In TABLE2 i have multiple values for a single ID , i need to select the record where Type = MANAGER

Table 1

Id	Manager
1
2

Table 2 

Id	name	Type
1	abc	Assistant
1	xyz	Manager
2	bcd	Manager
2	fgh	Assistant

Regards

Nagaraj

[MG86]

I know this is an older thread, but for future reference isn't it easier to just use the second lookup parameters of LOOKUPVALUE?:

LOOKUPVALUE(
   'Table2'[Name], 'Table2'[Id], 'Table1'[Id], 'Table2[Type], "Manager")

[v-lid-msft]

Hi @nagaraj ,

We can try to create a calculated column in 'Table 1' to meet your requirement:

Manager =
CALCULATE (
    MAX ( 'Table 2'[name] ),
    'Table 2'[Type] = "Manager",
    'Table 2'[id] = 'Table 1'[id]
)

Best regards,

[nagaraj]

@v-lid-msft 

 HI, 

 Thanks for the help, was able to resolve this using the below 

Property_Manager = CALCULATE(FIRSTNONBLANK(Table2[Name],1),
filter(all(Table2),
Table2[Type] ="Property Manager" &&
'Table1'[Id] = Table2[Id]))

[tctrout]

The approach I would explore in M Code, not DAX.. This can be done within the GUI interface

Create copy of Table 2

Filter for Manager only

Remove duplicates on ID, if eixists.  This should provide you a table of manangers, their names and ID

Go to Table 1 and merge table 2 using ID as the key/lookup value.

[nagaraj · Accepted solution]

Hi @tctrout 

Thanks for the help, was able to resolve using below 

Property_Manager = CALCULATE(FIRSTNONBLANK(Table2[Name],1),
filter(all(Table2),
Table2[Type] ="Property Manager" &&
'Table1'[Id] = Table2[Id]))