Forum Discussion
Solved
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...
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.
DataZoe
Microsoft Employee
Microsoft Employeemrichman 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 view
5. Now use the new Letters[Letter] in your matrix.
I've attached the PBIX too.
