I’m still tinkering as well as changing jobs, so don’t think I’ve forgotten about you.
1 Like
I never thought you forgot - was more thinking in line of the tinkering as that’s what this case has got me doing in the past week 
I wish you all the best in your new job though
I haven’t made further progress from my last update on this case - taking a step back to see what eludes me in the imlementation
Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum
I’m still actively working a solution on this one. Is it possible to keep it open a bit longer, to keep continuity on this discussion? Thanks.
- Brian
2 Likes
Hi Team, I’d like to keep this still open as work is still ongoing to find a solution.
How do I ‘unsolve’ it?
You should be able to go into post #20, click on the three dots next to Reply and uncheck the solution box.
FYI – I’ve got the whole evening blocked off to work on this if needed. Hope to have significant progress for you by tomorrow.
- Brian
thanks @BrianJ - unchecked the solution box
Much appreciate your time & effort. Keeping fingers crossed & looking forward to learning a trick or 2
Incredibly fun and challenging problem. Making progress, but have a question - can I always count on the maximum number of levels being 4, or do I need to account for that being dynamic as well?
Thanks.
- Brian
number of levels to account for dynamism. Some managers will be only 3, while some may go up to 7!
One thing I noticed when I worked on it, also, is that a manager could have employees with mixed levels…for example a manager could have an employee with no management role reporting to him/her AND as well another employee with management role. In the prototype excel mockup, as a practical example, we could have a scenario where Person 2 reports to LM 3 instead of reporting to LM 1
OK, 7 is the number I needed (frankly, I was hoping it would be 4, but you have to play the cards you’re dealt, not the cards you want…). If you have Russo and Ferrari’s “DAX Patterns”, the last chapter on Parent-Child Hierarchies is very useful, and in it they state “to create the right number of calculated columns, you must know in advance the maximum depth of the hierarchy. Otherwise you have to estimate it because the number cannot change dynamically”.
I spent last night sketching this problem out and rubber ducking potential approaches, which convinced me of two things:
- we are going to have to eat this elephant in small pieces. As you’ve identified, the self scoring portion is easy. However, jumping from that straight to the full team calculation is too big and complex a piece to digest. The approach I plan to take is to start with a two level hierarchy (A,B and H), predicated on a reshaped supporting table that defines the direct reporting relationships:
This type of supporting table should make writing the DAX much easier. Then once we’ve validated that we’ve got a two level hierarchy working, we keep building out until we get to level 7.
- currently, we have DAX doing too much of the work. Given the static nature of the hierarchical relationships, a lot of this task I think can and will be handled ultimately via Power Query. In order to start doing that, can you please upload the “20190817 Sample data.xlsx” file?
While so far I’ve been working primarily at just a conceptual level, I did mark the date table, and change the DAX in the Team L0 calculated column to:
Team L0 =
IF(Employee[Head role] = "n",
Employee[Employee ID],
BLANK()
)
Rather than waiting until I have a full 7–level solution, I will post progress as I go so that you, @mickeydjw and I can work together in moving this one forward.
- Brian
1 Like
Hi @BrianJ
Indeed this is an interesting approach you bring up. I’m all excited. Yes I do have the DAX Patterns and will review that portion. What intrigues me is the use of Power Query as you’ve mentioned: I doubt I ever come across hierarchies built in PQ (I’m usually one to go as far as possible in PQ before handing over to DAX for modelling).
Thanks for the learning opportunity.
Attached is the sample data file.
20190817 Sample data.xlsx (20.7 KB)
Thanks very much for promptly posting the data file. In rereading my previous message, I can see that it was unclear. Your employee table and the created hierarchies are perfect - exactly the form that Russo and Ferrari recommend.What I was referring to was the supporting tables that I think we will need to create in order to iterate over to develop the team scores. Because these hierarchies will be static (in terms of not changing during a single session), we can materialize these as physical tables using Power Query, rather than as virtual table variables in DAX measures. This will definitely make our measures less complex and the overall analysis easier to debug.
I haven’t worked much with PATH functions in the past, so this will be a good learning experience for both of us in different ways.
More to follow soon…
- Brian
P.S. I came across this video at lunch today. I’ve never seen this done in Power Query either, but some interesting techniques here:
Hi @Bare, we’ve noticed that no response has been received from you since the 5th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
My apologies to for the slow progress on this. I have a huge project that is scheduled to go to production at the end of March, and am spending most of my waking hours trying to ensure we meet that deadline. Thus, I haven’t had a fraction of my usual time dedicated to the forum lately. However, this is a fascinating problem and I’m committed to getting you a solution as soon as I have time to get back to it, if you, @mickeydjw and others haven’t solved it by then. Again, sorry for the delay.
- Brian
Hi @BrianJ
No worries - these are coronavirus times. With more time spent at home, I took a different angle to the challenge and came up with something that seems promising. I am working on it right now and will share once it proves positive. Basically, I’m going mathematical and finding another of calculating the team values without depending on direct reporting. Not clear now I know, but with more time at home (due to Covid-19) I have more time.
Cheers & stay safe / healthy
Bare
Hi @BrianJ
Hi @mickeydjw (I trust your new job is going well)
Big breakthrough, somewhat!
Not in Power BI per say but rather in the mathematics logic behind what the objective is. Previous I have based the calculation of the team score dependent on the total score of the direct reports, whose value in turn depend on their reports and so on. I discussed this with a math expert & we came up an alternative that seems easier to implement in Power BI. Rather than a hierarchical layered approach, we instead used a flat list of all employees within a team head dept to calculate his / her team score. Amazingly, the results turned out the same as the complicated hierarchical approach!
What do I mean? Using the image below for team head ‘Emp H’ as reference:
- The virtual table displays the full list of employees with Emp H at the top - no reliance on levels - per month
- I used LOOKUP function to obtain the ‘LM team weight’ column
My challenges:
-
I need to now scale up each employee weight by dividing their weight by the total weight per manager per month. For example, in the image, Emp L scaled weight will be = 1 / (1 + 0.8) = 0.56
-
(1 + 0.8) is the total weight for employees reporting to Emp K for that month
-
Once I have this scaled weight, next is to multiply this with the team weight of the respective manager. Stayin with our example, Emp K active weight is thus 0.56 * 0.15 = 0.0084
-
For direct reports (i.e. if manager column value equals the manager being evaluated, no scaling is done. The actual weight is used. For example, Emp K reports directly to Emp H & hence active weight is 0.85
-
It is this active weight to used with each employee score to calculated the weight score and give the final team value
Any insights how I can walk through steps 1 through 5? I believe it is now way lot easier to implement but still need help.
I have attached the updated PBI file with the virtual table 3. I also attach the excel prototype to demonstrate this alternative math method. both files below.
Many thanks for your time & help- much appreciate
Bare
Emp Dashboard v5.pbix (166.1 KB)
overview of prototype.xlsx (17.5 KB)
Hi @Bare, It’s great to know that you are making progress with your query. Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate. For further questions related to this post, please make a new thread. More details can be found here - https://forum.enterprisedna.co/t/asking-questions-on-the-enterprise-dna-support-forum/30
My sincere apologies @EnterpriseDNA
I totally misjudged the rules around asking question as I thought I should provide updates on the topic.
Anyway, will close this thread.
Many thanks
Definitely open a new thread starting with your info on Post #42. I’m excited about the new mathematical approach you’ve taken and would like to dive in and work with you and @mickeydjw on this new tack.
I should be able to take a break this weekend from this production deadline I’m crunching on this week, and really dive into this (it’s not like I’m going anywhere these days…).
- Brian
1 Like
Thanks @BrianJ
Closing this thread.
Opened new thread:
Thanks for all your help & assistance. And you are right - going nowhere for me as well!
1 Like