Forum Discussion
Direct Query - Multiple sorting in 100% stacked bar chart by Akvelon 1.2.3
Hi,
I have a visual like the below using Direct Query and would like to do custom sorting by different variables simultaneously.
1. Sort by TIMESTAMP in decending order (ie. 5/jun, 4/jun, 3/jun...etc) => I used the default sorting in the chart by clicking on the three dots on the top right corner and choose "Sort By" - "Timestamp - "Sort descending".
2. Sort by DESCRIPTION in custom order based on the following list. => I used the "Sort by Column" function in the model and select the "Description" column and choose "Sort by -> SequenceId". This way allows me to sort the Description based on the sequence order as per the SequenceId.
| SequenceId | Description |
| 1 | Purchase Order Import |
| 2 | Sales Order Import |
| 3 | PO Control Export |
| 4 | Booking Import |
| 5 | Cargo Booking Import |
| 6 | CFS Receipt Import |
| 8 | Shipment Auth Export |
| 9 | Shipping Import |
| 11 | Shipment Status Import |
| 12 | Load Manifest Export |
| 13 | Advice Of Departure Export |
| 14 | Strix Export |
| 15 | Customs Clearance Import |
| 17 | Landside Import |
| 19 | Landside Arrival Export |
| 20 | Landside Delivery Booking Export |
| 21 | Landside Delivery Receipt Export |
As per above screen shot, the sorting is correct for 5/Jun but incorrect for the rest of the dates.
For example, the sorting in 2/Jun is not correct because Shipment Auth Export / Cargo Booking Import / Shipping Import should not appear at the bottom of the chart based on the sequential order given in the above table.
I want my chart to be sorted with Timestamp in descending order AND Description in ascending order based on SequenceId.
Any idea how I can overcome this problem?
Best regards,
Emily
12 Replies
Hi Anonymous ,
2. Sort by DESCRIPTION in custom order based on the following list. => I used the "Sort by Column" function in the model and select the "Description" column and choose "Sort by -> SequenceId". This way allows me to sort the Description based on the sequence order as per the SequenceId.
SequenceId Description 1 Purchase Order Import 2 Sales Order Import 3 PO Control Export 4 Booking Import 5 Cargo Booking Import 6 CFS Receipt Import 8 Shipment Auth Export 9 Shipping Import 11 Shipment Status Import 12 Load Manifest Export 13 Advice Of Departure Export 14 Strix Export 15 Customs Clearance Import 17 Landside Import 19 Landside Arrival Export 20 Landside Delivery Booking Export 21 Landside Delivery Receipt Export Have you put [Description] column of the above sorting table into the chart field to replace [Description] column of your fact table?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Icey Yes I did exactly the same as what you mentioned below but the sorting still didn't work -> I put the [Description] from the below table (with Sort By Sequence Id) into the chart axis and I did not use the [Description] from the fact table, so ideally the chart sorting should follow the sequence according to the Sequence Id but unfortunately it did not 100% follow to the desired sequence.
Best regards,
Emily
Hi Anonymous ,
So weird. It works well on my side.
Can it work in other charts?
Best Regards,
Icey
Hi Anonymous ,
I may find the cause. This problem is caused by the visual principle rather than the model.
For the 1st column,
- show [Description] of "Succeeded" containing values/records, based on custom order;
- show [Description] of "Failed" containing values/records, based on custom order;
- For other [Description] containing no values/records, sort by the order of values/ records appearance, so the first one of the rest [Description] is "Shipment Auth Export", which appears values/records first in 2nd column.
I have tried my best to explain clearly and hope you can understand.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Icey Thank you so much for your clear explanation and the animation. I got your point and it seems the problem came from the design of the custom visual rather than from the data as what you said. I tried workarounds such as using DAX to enforce a value for the counting and not to leave it as blank for those Descriptions without data, unfortunately it didn't work for this type of chart.
Right now I don't have any other solutions to solve this matter unless if I give up this chart type. Anyway thanks for your help again and have a good day!
Best regards,
Emily
