Forum Discussion

rmcgrath's avatar
rmcgrath
Advocate II
1 year ago
Solved

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...
Need Help
Show and Tell
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

    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
danextian's avatar
danextian
Super User
1 year ago

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.