Forum Discussion
Calculated Table
Hello Experts,
Need Guidance with the below structure
Table1 with data :
| id | party | value |
| 1 | B | 10845 |
| 2 | B | 22921 |
| 1 | I | 20921 |
| 5 | I | 5807 |
| 7 | I | 40044 |
| 9 | B | 24634 |
| 9 | I | 11967 |
| 14 | I | 869 |
The Required Result Table :
| id | B | I |
| 1 | 10845 | 20921 |
| 2 | 22921 | |
| 5 | 5807 | |
| 7 | 40044 | |
| 9 | 24634 | 11967 |
| 10 | ||
| 14 | 869 |
Using Calculated table on Power BI desktop
Thanks & Regards....
I don't think it is possible to do a pivot operation dynamically in dax; only in power query. At least I can not think of any way to achieve this.
Hi BIswajit_Das
sjoerdvn is right.
DAX has a number of limitations, and if this is a calculated table, this is the only way to do it
Or you can try to export the data from the calculated table and import it again to use power query. But it's going to be very complicated.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hello BIswajit_Das ,
you can acheive the below in Power Query with pivot.
check it out https://youtu.be/Sm_NodeFvPg?si=cKZpyEYOpOR2FXnH
Hi BIswajit_Das
Please try this:
Click the Transform data in the Home pane:
Copy the origin table and paste it:
Select the party column and click the Pivot Column:
The result is as follow:
Finally, Close & Apply.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Table2 = ADDCOLUMNS(VALUES(Table1[id]) ,"B", CALCULATE(VALUES(Table1[value]),ALLEXCEPT(Table1,Table1[id]), Table1[party]="B") ,"I", CALCULATE(VALUES(Table1[value]),ALLEXCEPT(Table1,Table1[id]), Table1[party]="I") )Hello sjoerdvn
Thanks for responding
Yeah we can do it this way
But we have mention column names each time
Isn't there any other way to acheive this?I don't think it is possible to do a pivot operation dynamically in dax; only in power query. At least I can not think of any way to achieve this.
Hello Anonymous, Idrissshatila
Thanks for responses but as I mentioned above my table is a calculated table which is not gonna show in transform data section
That's why I wonder if there's another way to do so..
Thanks & Regards...
