March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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]))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
85 | |
69 | |
61 |
User | Count |
---|---|
138 | |
120 | |
109 | |
99 | |
97 |