Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I currently have this data:
And I wish to have the following results:
How can I create this using the "New Table" function? I might also create a new excel sheet with the "Categories". How then can I utilize DAX
Thanks in advance!
Solved! Go to Solution.
Hi @vutruong91 ,
The easist way is on the query editor, select the columns Project and choose unpivot columns. This is my recommend way.
However you can also do this in DAX you can do the following formula:
DAX_Table = UNION ( SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project A]; "FOR"; "Project A" ); SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project B]; "FOR"; "Project B" ); SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project C]; "FOR"; "Project C" ); SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project D]; "FOR"; "Project D" ) )
Check attach the PBIX file with both options.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIs there a way to do this for an unknown / changing number of project values, so that formulas don't have to be rewritten every time a new project value is added to the data table? Thanks!
Hi
You should use the unpivot on the power query.
That way there is no need to rewrite
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @vutruong91 ,
The easist way is on the query editor, select the columns Project and choose unpivot columns. This is my recommend way.
However you can also do this in DAX you can do the following formula:
DAX_Table = UNION ( SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project A]; "FOR"; "Project A" ); SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project B]; "FOR"; "Project B" ); SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project C]; "FOR"; "Project C" ); SELECTCOLUMNS ( Projects_Dax; "DESCRIPTION"; Projects_Dax[Description]; "VALUE"; Projects_Dax[Project D]; "FOR"; "Project D" ) )
Check attach the PBIX file with both options.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
One way of manual unpivoting using DAX could be to select each column individually and then append it beneath other column
Table = UNION ( SELECTCOLUMNS ( Table1, "For", "Project A", "Description", [Description], "Value", [Project A] ), SELECTCOLUMNS ( Table1, "For", "Project B", "Description", [Description], "Value", [Project B] ) )
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |