Forum Discussion
Rearrange a dimension table?
I have a dimension table for "Delinquency" in an Accounts Receivable Power BI report. It looks like this:
Is there a way to have "Current" be the first row and the rest follow as is? I tried adding "00_" to Current ("00_Current") but it didn't work
If you want to use DAX, you need a CC :
SortOrder = SWITCH( TRUE(), 'Delinquency'[Delinquency] = "Current", 1, 'Delinquency'[Delinquency] = "01_to_15_Days", 2, 'Delinquency'[Delinquency] = "16_to_30_Days", 3, 'Delinquency'[Delinquency] = "31_to_60_Days", 4, 'Delinquency'[Delinquency] = "61_to_90_Days", 5, 'Delinquency'[Delinquency] = "91_and_Over", 6, 7 -- Default if none match )Then you can sort your column based on SortOrder.
If you want to use PQ, you can add a custom column like below :
= if [Delinquency] = "Current" then 0 else if [Delinquency] = "01_to_15_Days" then 1 else if [Delinquency] = "16_to_30_Days" then 2 else if [Delinquency] = "31_to_60_Days" then 3 else if [Delinquency] = "61_to_90_Days" then 4 else if [Delinquency] = "91_and_Over" then 5 else 6
3 Replies
If you want to use DAX, you need a CC :
SortOrder = SWITCH( TRUE(), 'Delinquency'[Delinquency] = "Current", 1, 'Delinquency'[Delinquency] = "01_to_15_Days", 2, 'Delinquency'[Delinquency] = "16_to_30_Days", 3, 'Delinquency'[Delinquency] = "31_to_60_Days", 4, 'Delinquency'[Delinquency] = "61_to_90_Days", 5, 'Delinquency'[Delinquency] = "91_and_Over", 6, 7 -- Default if none match )Then you can sort your column based on SortOrder.
If you want to use PQ, you can add a custom column like below :
= if [Delinquency] = "Current" then 0 else if [Delinquency] = "01_to_15_Days" then 1 else if [Delinquency] = "16_to_30_Days" then 2 else if [Delinquency] = "31_to_60_Days" then 3 else if [Delinquency] = "61_to_90_Days" then 4 else if [Delinquency] = "91_and_Over" then 5 else 6Add a new column and use sort by feature and hide from report view
https://help.zebrabi.com/kb/power-bi/add-a-sorting-column/Basing on the screenshot, you can add another column in Power Query by extracting the first two characters which will be 01, 16, 31, 61, 91 and Cu. Then use that column sort the Delinquency column by. They will be a text string but they will be in the correct order.
