Sort by time

[elois33]

Hello, 

How can I sort my martice table by hour, starting from the earlieast departure time for each "id bus"  

Here is an example of my data in Excel. I already tried right click, sort by departure but it doesnt work.

I believe the error is here to this calcul I've used  to find the average of arrival time so I believe I have filtered by IDbus by default. Any thoughts? 

AVG departure = CALCULATE(AVERAGE('hours'[departure_time]),FILTER('BUS','BUS'[ID bus]=EARLIER('BUS'[ID bus])))

Thanks,

[elois33 · Accepted solution]

Hello all, 

I found a solution for this and it seems to be the right one for now, so I am sharing it with you in case somebody has the same issue . 

-To sort by time, I created a new column where I turned hour into second, I added it to my visual, I selected sort by second and then I hide it from my table. 

-To sort my table by geographical order, I created a new column where I multiplied longitude x latidude, I added to my table and I sorted by the result of this new column.

Thank you all for your time and help 

Kind regards, 

Elois

[Ashish_Mathur]

Hi,

Show the result you are expecting.

[v-xiaotang]

Hi @elois33 

Thanks for reaching out to us.

if you want to sort table by departure, you can hover over here and click on the triangle icon

if you want to sort table by departure and ID, you can create the measure below,

rank = RANKX(FILTER(ALL('Table'),'Table'[ID bus]=MIN('Table'[ID bus])),CALCULATE(MIN('Table'[departure])),,ASC)

Best Regards,

Community Support Team _Tang

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

[elois33]

Thank you @v-xiaotang and @Ashish_Mathur 

I wanted to share with you the dashbord but it says that the file type pbix is not supported, so Iam attaching a screenshot. 😕

-You have a matrice table with departure/arrival hours, weight/lenght of cargo, days, ID mission and itinerary. You have also a slicer which allows my colleuges seach for the itinerary they need. 

-I'm trying to sort my matrice table by the departure time, so when I have more than one mission for the same itinerary I will have all the missions starting from the earlier one. 

-The second thing that I was asked to do and I'm struggling is to sort my table based on the order of the selected value on the slicer. For example when I search for Kansas/Colorado and Colorado/Oklahoma I want my matrice table to have that specific order and not put the itineraries by default in alphabitical order. I have a lot of missions and itineraries and the order of the itineraries is based on my colleuges needs. 😕 I know that maybe with "selected value" I could make things work, what do you think? 

Thank you very much for your time and help, it's much appreciated 

Kind regards, 

Elois

 

[Ashish_Mathur]

Sharing a screenshot does not help at all.  Share a dataset to work with (a table that can be pasted in MS Excel) and on that data, show the expected result.

[elois33]

Hello @Ashish_Mathur, thank you for your response. 

Please find bellow the link to my power bi file on google drive. Hope it works 

Regards, 

Elois

https://drive.google.com/file/average_filtered_by_categories

[Ashish_Mathur]

You have not shown the expected result.  What result are you expecting?

[elois33]

Hello @Ashish_Mathur , Thank you for your reply. I don't know how to show you the expected result but I can explain you what I'm trying to achieve and I attached the screenshot bellow.

-I want to sort my martice table by the departure time so I can have the ID sorted by the earliest departure time.

-The second thing is that when I choose a specific itinerary on the slicer on the right, I want to have the same order of this itinerary on my matrice table. For example, when I select Kansas/Colorado and then Colorado/Oklahoma, I want to sort the matrice table by this specific order that the user has chosen. 

Thank you very much for your help, I appreciate it. Hope you can figure out a way to solve my 2 weeks problem 😪

Regards, 

Elois

[elois33]

Thank you @v-xiaotang ! I used the the measure that you suggested and is says : "the second argument of the calculate function is needed" 😕 Any ideas what can I do? 

Thank you very much

[v-xiaotang]

Hi @elois33 

Thanks for your reply.

Please check if rankx() is used correctly? 

rank =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[ID bus] = MIN ( 'Table'[ID bus] ) ),
    CALCULATE ( MIN ( 'Table'[departure] ) ),
    ,
    ASC
)

RANKX function (DAX) - DAX | Microsoft Learn

Best Regards,

Community Support Team _Tang

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

[elois33]

@Greg_Deckler

[elois33]

@amitchandak