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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
elois33
Helper I
Helper I

Sort by time

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])))
 

Capture d’écran 2022-10-17 094622.png

 

Thanks,

1 ACCEPTED SOLUTION
elois33
Helper I
Helper I

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

View solution in original post

12 REPLIES 12
elois33
Helper I
Helper I

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
Super User
Super User

Hi,

Show the result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1666061491171.png

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)

vxiaotang_1-1666062200345.png

 

 

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.

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

 

testpowerbi.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 😪

powerbitest2.jpg

Regards, 

Elois

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

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

vxiaotang_0-1666232023286.png

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
Helper I
Helper I

elois33
Helper I
Helper I

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.