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
AmiraBedh's avatar
AmiraBedh
Super User
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