Question in L1 Modelbuilding exam has same Role name as Department name.




Please can some one help me on how to do this as Department as well as Role name have same name Executive and same code Ex in them.

How will data flow in to the module correctly in this case.





Roles list data is 

ExecutiveAll RolesEX 
Senior DirectorAll RolesSD 
DirectorAll RolesD 
Senior ManagerAll RolesSM 
ManagerAll RolesM 
Team LeaderAll RolesTL 
Staff MemberAll RolesS 


Dept CodeDept Name
HRHuman Resources


Pivot view of Employee drivers by role is as below.



I got confused and was struck in this. There is no common dimension between source and target in this case. Got the following 2 questions wrong due to this.

Please can someone help me in solving this. 



Balarama Krishna

Best Answer

  • BrunoRodriguez

    Hello @krishnadbr ,


    You have provided the detailed information about the lists, but not about the modules we are going to be correlating, and need to have a common dimensionality.


    As you well know, you have been required to provide the bonus for each employee depending upon the Employee's role and department. 


    • EMP03 provides you the information by role and department, but is not connected with each employee.
    • SYS08 has teach employee´s department and role, but does not have the salary.

    If you think about the LOOKUP function (X[LOOKUP:Y1, LOOKUP:Y2]), the X represents the source module - what you are looking for (Bonus in this case)- while the Y corresponds with the correlations you want to establish (Role and department).


    I would encourage you to read the following, written by @AlejandroGomez :


    Have a go at it and come back if you still do not manage to resolve it to compare answers!

    Kind regards,

    Bruno Rodriguez


  • @BrunoRodriguez 

    I am still brain storming on this. Yesterday I spent over 1 hr for applying the formula LOOKUP in same lines as you mentioned. 

    I tried the following even now and still not able to crack this.





    IF Employed? THEN Salary *'Emp03 Employee by Role'.Bonus % [LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'E3 New Role'.All Roles ] ELSE 0


    I got the following error.


    I also tried to pivot my EMP03 in all possible ways and tried this yesterday. 


    Please help me.


    Balarama Krishna

  • @BrunoRodriguez 

    Also tried this.


    I am not able to add E2 as top level for E3 As I am getting the following error.



    Please can you help me with this. Totally confused as E3 has executive EX under role context and E1 has Executive Ex as Department.


    Balarama Krishna