Sort Values from Calculated Column

[AkshayManke]

I have created a Calcucated Column and getting output as below.

Overdue

Due Today

Not Due Today

I have added that column in a Matrix in Columns area. But i am getting the column sequence as below.

Whereas i need in the below sequence.

Overdue | Due Today | Not Due Today

How can i do that please?

[Bipin-Lala · Accepted solution]

Hi @AkshayManke,

Sorting the "Due Status" column as per your expectations is possible. We can use the Sort By Column functionality in Power BI to achieve the desired sorting, though we need to keep in mind to avoid circular dependency while sorting. Please follow the steps below - 

• I am assuming you created the Due Status calculated column using some logic in DAX. Assume the following sample data I created

• We need to assign a number/ code to each due status which will be used for sorting the column. If you create a calculated column "Due Status Order" based on Due Status column itself, it will lead to circular dependency issues, as shown below - 

Due Status Order = 
SWITCH(
    Products[Due Status],
    "Overdue", 1,
    "Due Today", 2,
    "Not Due Today", 3
)

• Hence instead, create a calculated column with the exact same formula as your Due Status column, just replace the values "Overdue", "Due Today" and "Not Due Today" with 1, 2 and 3 respectively. This means we are just creating a calculated column for ordering statuses based on the source column itself. I will call this column as "Due Status Order Source"
• Select Due Status Column and click on Column Tools Tab -> Sort By Column -> Due Status Order Source

Voila! The columns in the matrix should now be sorted

Before Sorting:

After Sorting:

This behaviour is very well explained using the following diagrams available here

Let me know if you have any questions!

[_AAndrade · Accepted solution]

Hi @AkshayManke;

You can add a new column to set an index for that column, using something like this:

Index = 
SWITCH( 
    TRUE(), 
    T_OutboundDelivery[DueStatus] ="Overdue", 1,
    T_OutboundDelivery[DueStatus] = "Due Today", 2,
    T_OutboundDelivery[DueStatus] ="Not Due Today",3
)

And after select the column where you have Overdue information and sort that column by the index column that you created, like this:

[Tom02 · Accepted solution]

I had the same problem as @AkshayManke ,

I solved it by doing this:

I made a conditional column, with the ranks I wanted.

So in this case, when Overdue = 1, Due Today = 2, Not Due Today = 3 (called ranks).

Then I made this ranks column a number type (I also made it a sum).

Then I sorted the table on the basis of the ranks column. This causes that the initally column also got sorted in the right way.

When I tried again in the matrix, I got the sequence I wanted.

Maybe this also helps for you.

[_AAndrade · Accepted solution]

Hi @AkshayManke;

You can add a new column to set an index for that column, using something like this:

Index = 
SWITCH( 
    TRUE(), 
    T_OutboundDelivery[DueStatus] ="Overdue", 1,
    T_OutboundDelivery[DueStatus] = "Due Today", 2,
    T_OutboundDelivery[DueStatus] ="Not Due Today",3
)

And after select the column where you have Overdue information and sort that column by the index column that you created, like this:

[AkshayManke]

Hi @_AAndrade,

I tried with your approach too. But getting below error. Could you please suggest next steps?

Thanks and Regards,

[_AAndrade]

@AkshayManke,

This measure is saying that you have more than one index for the same status, so you have some issue in your data or in the Index formula.
Could you please share more information?

[Bipin-Lala · Accepted solution]

Hi @AkshayManke,

Sorting the "Due Status" column as per your expectations is possible. We can use the Sort By Column functionality in Power BI to achieve the desired sorting, though we need to keep in mind to avoid circular dependency while sorting. Please follow the steps below - 

• I am assuming you created the Due Status calculated column using some logic in DAX. Assume the following sample data I created

• We need to assign a number/ code to each due status which will be used for sorting the column. If you create a calculated column "Due Status Order" based on Due Status column itself, it will lead to circular dependency issues, as shown below - 

Due Status Order = 
SWITCH(
    Products[Due Status],
    "Overdue", 1,
    "Due Today", 2,
    "Not Due Today", 3
)

• Hence instead, create a calculated column with the exact same formula as your Due Status column, just replace the values "Overdue", "Due Today" and "Not Due Today" with 1, 2 and 3 respectively. This means we are just creating a calculated column for ordering statuses based on the source column itself. I will call this column as "Due Status Order Source"
• Select Due Status Column and click on Column Tools Tab -> Sort By Column -> Due Status Order Source

Voila! The columns in the matrix should now be sorted

Before Sorting:

After Sorting:

This behaviour is very well explained using the following diagrams available here

Let me know if you have any questions!

[AkshayManke]

Hello @Bipin-Lala , Bipin,

I tried with the steps but getting the above error while trying to sort column based on the newly created. Could you please help what additionally needs to be done?

Thanks and Regards,

[Bipin-Lala]

Hi @AkshayManke,

It seems like for each Status value, you are assigning multiple values of S Sort, which is causing this issue. Could you please share the DAX formula you used for creating the Status and S Sort columns?

Also, some more information about the data would be helpful, such as does the Status column contains any blanks, etc.

[AkshayManke]

Hello @Bipin-Lala, @_AAndrade, and @Tom02,

Apologies for late reply. Actually after trying all the possibilities i simply added numbers in prefix in the expected order and now i am getting the expected output. Below the DAX which finally i used.

Status = 
IF(
'Tasks List'[Completion] = "Not-Started" && 
'Tasks List'[PlannedEndDate]='Tasks List'[Today] ,
"2. Due Today",
IF(
'Tasks List'[Completion] = "Not-Started" && 
'Tasks List'[PlannedEndDate]<'Tasks List'[Today],
"1. Overdue",
"3. Not Due Today"
)
)

However many thanks for your quick suggestions and help.

I am marking both of your responces as solutions.

Thanks and Regards,