Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |