Forum Discussion
How to create dynamic columns within Pivot - PowerBI?
Hi Community,
Hope you're doing well. Is there any way to create a dynamic column in pivot? I want to rearrage the order...
Instead of ABC I want to change the order for example to CBA, BAC or any order I would prefer. Thanks in advance!
mrichman You can change the order by changing the default sort order of the column used for A, B, C. One way I do it is to create a lookup/dimesnion table with a different specified ordering.
One way is to:
1. Go to "Modeling" ribbon, choose "New Table"
2. Enter this DAX expression:
Letters =DATATABLE("Letter",STRING,"Sort Order",INTEGER,{{"A",3},{"B",2},{"C",1}})3. Click on the newly created [Letter] column in 'Letters' table, and in "Column Tools" ribbon choose "Sort by Column", and pick [Sort Order].4. Join Letters[Letter] to Table[Letter] via relationship in modeling view5. Now use the new Letters[Letter] in your matrix.
I've attached the PBIX too.
2 Replies
mrichman You can change the order by changing the default sort order of the column used for A, B, C. One way I do it is to create a lookup/dimesnion table with a different specified ordering.
One way is to:
1. Go to "Modeling" ribbon, choose "New Table"
2. Enter this DAX expression:
Letters =DATATABLE("Letter",STRING,"Sort Order",INTEGER,{{"A",3},{"B",2},{"C",1}})3. Click on the newly created [Letter] column in 'Letters' table, and in "Column Tools" ribbon choose "Sort by Column", and pick [Sort Order].4. Join Letters[Letter] to Table[Letter] via relationship in modeling view5. Now use the new Letters[Letter] in your matrix.
I've attached the PBIX too.
