Direct Query - Multiple sorting in 100% stacked bar chart by Akvelon 1.2.3

[Anonymous]

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

[Icey]

Hi @Anonymous ,

I may find the cause. This problem is caused by the visual principle rather than the model.

For the 1st column,

1. show [Description] of "Succeeded" containing values/records, based on custom order;
2. show  [Description] of "Failed" containing values/records, based on custom order;
3. 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.

[Anonymous]

@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

[Icey]

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.

[Anonymous]

@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

[Icey]

Hi @Anonymous ,

So weird. It works well on my side.

Can it work in other charts?

Best Regards,

Icey

[Anonymous]

@Icey  I haven't test for other visuals because I really need to use this plug-in one as it suits our needs with multiple variables.

Do you mind to share your testing file with proper sortings for both of the Timestamp and Description?

Thanks!

Best regards,

Emily

[Icey]

Hi @Anonymous ,

Please check the attached .pbix file. I tested with SQL Server Datebase.

Best Regards,

Icey

[Anonymous]

@Icey  Sorry that I can't open your file as it needs SQL Server credentials for your DB.....

Are you able to upload sample static data for the Sales table with Import mode?

Thanks!

Best regards,

Emily

[Icey]

Hi @Anonymous ,

Please check.

Best Regards,

Icey

[Anonymous]

@Icey  Thank you very much for your sample file.

I tried it in my computer and the sorting is correct for both of the [Timestamp] and [Description] in your file.

However, it is still not working from my end because my [Timestamp] is a string format rather than a date format. Since I need to use Direct Query for my report, I intentionally created a [Timestamp] table in string format because SQL Server do not have default format in dd-MMM-yyyy format.

Here is what I have done in Power Query to get the Timestamp in string format.

Power Query result from the above Timestamp_SQL query:

In the PowerBI screen, I select [Timestamp_Date] and choose "Sort by Column" from the top menu then sort it by [1st Occurence Timestamp] which is identified as DATE field in PowerBI.

Going back to the chart visual, I put Timestamp_SQL[Timestamp_Date] as the "Column By" in the chart and Sort By Timestamp_SQL[Timestamp_Date] in descending order. Since the sorting for both of the [Description] and [Timestamp] is based on another table rather than based on the fact table, so it results to the following problem.

I have tried your way to only drag & drop the Timestamp field into "Column By" of the chart but that results to a wrong date format just like what is shown in your sample. Tha was the reason why I manually converted the [Timestamp] from date format to string format, and I put the string format of Timestamp into the chart.

Any idea how to solve the above problem?

Thanks a lot!

Best regards,

Emily

[Icey]

Hi @Anonymous ,

I changed my model based on your description, but no issue. 

Do you mind creating a dummy file to reproduce the issue and sharing it to me? Please remove sensitive information. It is suggested to upload your file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey  I tried to re-do another file with Import mode and did similar chart as before. I reproduced the problem in the following file and you can download at the following link in Gdrive:

https://drive.google.com/file/d/1QK4Kq4xAnY0CZuQx9yE7Ld3ac0NU2Ftt/view?usp=sharing

Thanks!

Best regards,

Emily