Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Fabric Community.
I have searched over the forum (and other sources), but I am unable to make a same table TEXT lookup function to work.
I have a raw data table with Employee name, Empoyee ID and manager ID and I want to create columns with up to 7 levels down of reporties.
This is what the data looks like vs what I want to built:
Employee Name E-ID Manager-ID
Andrew Anderson a1a34
Mary Jane m2j23 a1a34
Peter Parker p3p45 a1a34
Jim Smith j4s43 m2j23
Jim Johnson j3j98 m2j23
Katy Sons k8s45 j4s43
Mark Magnuson m2m65 k8s45
I have tried
L2_manager-ID =
IF(ISBLANK([#"Table"[#"Manager-ID"],
"",
LOOKUPVALUE(#"Table"[E-ID],
#"Table"[Employee Name],
#"Table"[#"Manager-ID"]))
------------------------------------------
L3_manager-ID =
IF(ISBLANK([#"Table"[#"L2_manager-ID"],
"",
LOOKUPVALUE(#"Table"[E-ID],
#"Table"[Employee Name],
#"Table"[#"L2_manager-ID"]))
------------------------------------------
L4_manager-ID =
IF(ISBLANK([#"Table"[#"L3_manager-ID"],
"",
LOOKUPVALUE(#"Table"[E-ID],
#"Table"[Employee Name],
#"Table"[#"L3_manager-ID"]))
...............................
Solved! Go to Solution.
if you really need to use lookupvalue it should be something like this.
Instead of lookup value use PATHITEMREVERSE with PATH.
Create a new column as follow for L2-Manager then another one for L3-Manager changing the value 3 to 4.
Hi Bmejia
Unfortunately this will not work as the database does not have correct hierarcky all around. What I mean by that is that some computers have their own account and their manage is -null.
Also some servers have a manager attached to them but the manager has left the company and noone has changed them to the new person.
Is there any other way you can think of?
* I have tried your DAX code and I got an error that an ID must be both in employee and manager (this id was not the CEO)
I don't know if this would work, since the value is null then you can replace the null value with the E-ID, go into transformation and add a conditional column. Then use this column instead as your manager column.
As for the other concern It think regarding data not being updated. That would be a data management issue, which you can't control would probably would still get the same wrong data in your results.
if you really need to use lookupvalue it should be something like this.
You are a legend!
Kudos and accepted solution.
User | Count |
---|---|
83 | |
78 | |
62 | |
61 | |
49 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |