Forum Discussion

newpbiuser01's avatar
newpbiuser01
Icon for Helper V rankHelper V
3 years ago
Solved

Unpivoting in DAX

Hello,

 

I currently have 8 different tables that are related to one another and I'm pulling in a name from each of the tables to create a comma seperated list of reportees for each manager. Unfortunately, I can't do it in Power Query unless I merge the 8 tables one by one and given the size of the data and the number of tables (8) that's really messing up the performance! 

 

So instead I add the relationships between the tables and then generate the table 1 reportees column. What I'd like to do now is unpivot the Table 1 below to get each of the reportees for each manager (Table 2).

 

Table I

ManagerReportees
BobMike, Andy, Liz, Christine
SallyLiz, Christine
TomDave, Sally, Liz, Christine

 

I need to get this:

ManagerReportee
BobMike
BobAndy
BobLiz
BobChristine
SallyLiz
SallyChristine
TomDave
TomSally
TomLiz
TomChristine

 

Has anyone tried to do this and know how I could do it in DAX? 

Need Help
  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi newpbiuser01 

    Please try

    Table2 =
seleactcolumns (
    GENERATE (
        Table1,
        VAR String = Table1[Reportees]
        VAR Items =
            SUBSTITUTE ( String, ", ", "|" )
        VAR Length =
            PATHLENGTH ( Items )
        VAR T =
            GENERATESERIES ( 1, Length, 1 )
        RETURN
            SELECTCOLUMNS ( T, "Reportee", PATHITEM ( Items, [Value] ) )
    ),
    "Manager",
    [Manager],
    "Reportee",
    [Reportee]
)

     

     

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi newpbiuser01 

    Please try

    Table2 =
seleactcolumns (
    GENERATE (
        Table1,
        VAR String = Table1[Reportees]
        VAR Items =
            SUBSTITUTE ( String, ", ", "|" )
        VAR Length =
            PATHLENGTH ( Items )
        VAR T =
            GENERATESERIES ( 1, Length, 1 )
        RETURN
            SELECTCOLUMNS ( T, "Reportee", PATHITEM ( Items, [Value] ) )
    ),
    "Manager",
    [Manager],
    "Reportee",
    [Reportee]
)