Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to 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]))
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")
this is the only correct answer !
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,
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]))
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.
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]))
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
91 | |
84 | |
81 | |
49 |
User | Count |
---|---|
145 | |
142 | |
111 | |
71 | |
55 |