topic Unpivoting in DAX in DAX Commands and Tips

Unpivoting in DAX

newpbiuser01 — Tue, 07 Feb 2023 15:05:53 GMT

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

Manager	Reportees
Bob	Mike, Andy, Liz, Christine
Sally	Liz, Christine
Tom	Dave, Sally, Liz, Christine

I need to get this:

Manager	Reportee
Bob	Mike
Bob	Andy
Bob	Liz
Bob	Christine
Sally	Liz
Sally	Christine
Tom	Dave
Tom	Sally
Tom	Liz
Tom	Christine

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

Re: Unpivoting in DAX

tamerj1 — Sun, 23 Jun 2024 19:54:53 GMT

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] )

Re: Unpivoting in DAX

newpbiuser01 — Mon, 24 Jun 2024 13:50:17 GMT

Thank you!