Rearrange a dimension table?

[rmcgrath]

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

[danextian]

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.

[sevenhills]

Add a new column and use sort by feature and hide from report view

https://help.zebrabi.com/kb/power-bi/add-a-sorting-column/

[AmiraBedh · Accepted solution]

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