Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

New Column Creation

I want to create a new column based on the following logic:   this is how my dataset looks: Id -> Emp Name's ID Emp Name -> Employee Name Mgr Name -> The Employees Manager Apart from thes...
Need Help
Show and Tell
  • Nathaniel_C's avatar
    Nathaniel_C
    7 years ago

    Anonymous ,

     

    SelectEmpCombo = IF(EmpLevel[LEVEL]=1,LOOKUPVALUE(EmpLevel[EmpCombo],EmpLevel[Emp Name],EmpLevel[Mgr Name]))
    EmpCombo = CONCATENATE(EmpLevel[ID], CONCATENATE("," ,EmpLevel[Emp Name]))
    FindDelimiter = IF(EmpLevel[SelectEmpCombo]<> "",Find(",",EmpLevel[SelectEmpCombo]))
    ParentID = IF(EmpLevel[SelectEmpCombo]<>"",LEFT(EmpLevel[SelectEmpCombo],EmpLevel[FindDelimiter]-1))

     

    This should do it, although you may want to combine the columns.  I left them separate for ease in debugging. First we combine the two columns  [ID] and [Emp Name] into [EmpCombo] separated by ",". (This gives us a unique identifier for all. )Then we use IF() and LOOKUPVALUE () in [SelectEmpCombo. That gives us the answer, but then we need to separate out the Emp ID.

     

    So, we use IF() and FIND() in [FindDelimiter] to find how many characters the ID is. Finally we use IF() and LEFT() to get the ParentID.

     

    If this solves your issue, please consider marking it Solved.  KUDOS are nice too!:smileyhappy:

    Nathaniel_C 

Nathaniel_C's avatar
Nathaniel_C
Community Champion
7 years ago

Anonymous ,

Are you trying to create a calculated column to add to your table with dax?

 

Did my answer display how you would expect the ParentID to display?

 

I know we have some additional work to do with the new Level data that you described, but want to know if we are on the same page so far.

Thanks,

Nathaniel_C 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Nathaniel_C 

Hey, so the logic without the level part is correct.. but it throws an error in powerbi for me and i am not able to create that calculated column

But basically i need the same LOGIC taking into account the factor " WHERE LEVEL = 1

 

Thanks for your time!

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    7 years ago

    Anonymous ,

    The reason that the calculated column throws an error is that we need to search another table for the value. So the way around this would be to create a virtual table. However to verify that we are on the same page, using your original data, would we expect to see the following for the employee PRAK? Multiple entries on the EMP column, showing all their managers, not just direct reports?  (Columns are ID, Emp Name, Mgr Name, Level)

    ID            Emp Name   Mgr Name    LEVEL

    1TRALAW1
    2ERILAW1
    3LAWJE1
    4MELITRA1
    5JATRA1
    6HAZTRA1
    7CHRHAZ1
    8CATHAZ1
    9PRAKJA1
    10AKPRA1
    9PRAKTRA2
    9PRAKLAW3
    9PRAKJE4
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Nathaniel_C  Yes. that is exactly how the table looks! :)

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        7 years ago

        Hi Anonymous ,

        Think this is it. Apparently if we surround the LOOKUPVALUE() with an IF (), then it can look at the same table for the values. This is blank where the Level is not 1, or where we don't have the ID for the Mgr Name.

        Please consider marking this as your solution, or let me know if we have further work.

        Cheers,