Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LSINCK
New Member

Need Help with Custom Sorts

RTOs (Recovery Time Objectives) are sorted in duration order in Excel. Load Excel spreadsheet to BI and RTOs are still in duration order. However, in the charts (visuals) the duration RTO are out of orders - see below.  The sortings I found in BI are Ascending/Desending or a 'custom sort' perform on 1 values at a time (i.e. equal to or less than.

In Excel, the custom sort allows you the type in the order you've like the items to appear. The RTO duration appears in the correct in the pivot chart in Excel but in the incorrect order in BI. Seems like BI is the using the original RTO duration order and not the 'custom sorted' order.

LSINCK_0-1629234917618.png

 

1 ACCEPTED SOLUTION

Thank you all for your feedback. Another issue occurs  while using the Related fir custom sorting . 

 

I am using the RELATED DAX query and it works in some instances . Related tables 'Sheet1' and RTOTable. However, after the Related query is executed in the Sheet1 table, some values are blank even though there is an associated value.  All RTO_ID are listed except for "8" and "10". Why is this happening? See both tables below

 

Sheet1 Table after the RELATED query is executed

LSINCK_0-1629481436438.png

RTOTable

LSINCK_1-1629481568608.png

 

View solution in original post

6 REPLIES 6
v-yingjl
Community Support
Community Support

Hi @LSINCK ,

In Power BI Desktop, the sort feature would be set as values field by default. If there are the same values, depends on the shared-axis based on the initial alphabetical order.

 

For custom sort in Power BI Desktop, you can refer: 

  1. Sort a Column with a Custom Order in Power BI 
  2. Custom Sort Order Trick in Power BI & Power Pivot 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

@LSINCK Duration
You will need to convert those to seconds, add them and essentially convert them back more or less. Look at the links below, they should get you what you need.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you all for your feedback. Another issue occurs  while using the Related fir custom sorting . 

 

I am using the RELATED DAX query and it works in some instances . Related tables 'Sheet1' and RTOTable. However, after the Related query is executed in the Sheet1 table, some values are blank even though there is an associated value.  All RTO_ID are listed except for "8" and "10". Why is this happening? See both tables below

 

Sheet1 Table after the RELATED query is executed

LSINCK_0-1629481436438.png

RTOTable

LSINCK_1-1629481568608.png

 

Thank you so mujch v-yingjl! I needed your 2nd pair of eyes 🙂

Hi @LSINCK ,

Please check whether the field format is the same as the RTO table.

For example, I have found that one of the type fields in RTO table 'Weeks 3+'  while in Sheet1 Table is 'Week 3+' so that it would return blank in the Sheet1 Table.

 

Simliar issue with '72 Hours - 1 Week', check whether the spaces are the same etc.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Greg for your quick response. Seems like a LOT of work for something that can be done in a few seconds, using Excel (2010) via a custom sort. The data I am using is large and though your method would yield the desired results, this method is not practical.

I’m disappointed to learn the archaic way that Power BI does customize sorts. Again, thank you for your feedback which is greatly appreciated!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors