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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sort values of a report data table based on date

Hello everyone !

I'm new on Power BI and after several hours spent browsing the forum i decided to create my own topic. I'm doing my best to learn by myself but i'm hard-stuck on something.

 

Here is my situation :

I'm currently working on a project that links an Excel file from a SharePoint to a Power BI report in order to display sorted informations on a dynamic screen in a logistics warehouse. It's basically visual management and the dynamic screen works with an intergrated software (MagicInfo), so to display my tables i have to use the Public Incroporated Link generated by Power BI Online.

 

I have this Excel file on a SharePoint that is where we are modifying the data, it is not sorted and in order to sort it like i want i have to create a macro but i'm not allowed to do that.

 2020-11-23 16_02_54-Tableau Rippage (BDD).xlsx - Excel.jpg

 

I've imported this table on Power BI and separated the left and the right side into 2 differents queries (Table Left and Table Right)

2020-11-23 16_19_48-Tableau Rippage (SP) - Power BI Desktop.jpg

 

Here are my problems :

I want to make Power BI either respect the sorting of the Excel file (by disabling the auto sort per exemple) or create my own automatic sort based on the date (from most recent to last). But...

  1. I have no way to interact with the Publicated Report from the dynamic screen to sort it by myself
  2. I had to create my own FORMAT on the date (= FORMAT([Date_G],"ddd dd/mm") but now the filter uses the alphabetical order

 

TL:DR : I need to find a way to auto-sort my tables on my report based on the date but not the formated one, and keep the same sorting while i load the incorporated link on the screen.

 

I hope it's clear, otherwize tell me if you need more informations.

 

Best regards,

M. Collet

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Ok, i think i've found a solution but it's bit weird...

It appears that the default sort is based on the first value added to the table on a report.

 

So here I have added "Date" first and now it auto sorts the value in the ascent order of Date.

2020-11-24 13_45_25-Tableau Rippage (SP) - Power BI Desktop.jpg 

Then I tried to move the order afterward and it keeps it.

Problem solved !

 

I have one last question: Do you have any idea how this will evolve in next updates ?

 

Thanks !

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Ok, i think i've found a solution but it's bit weird...

It appears that the default sort is based on the first value added to the table on a report.

 

So here I have added "Date" first and now it auto sorts the value in the ascent order of Date.

2020-11-24 13_45_25-Tableau Rippage (SP) - Power BI Desktop.jpg 

Then I tried to move the order afterward and it keeps it.

Problem solved !

 

I have one last question: Do you have any idea how this will evolve in next updates ?

 

Thanks !

Anonymous
Not applicable

No and that's the point, nothing is sorted and filtered but somehow the order of the values is kinda random...

 

Here is an exemple of what i don't understand, i've created a new project with a new data table on excel and again it sorts it randomly ... See below :

 

Here is the excel table (not sorted)

2020-11-24 11_28_14-Tableau Rippage (BDD).xlsx - Enregistré.jpg

 

And here is the table on Power BI

2020-11-24 11_26_44-Untitled - Power BI Desktop.jpg

 

Help u_u

Anonymous
Not applicable

Thank you both for your answers, but Power BI keeps sorting the values in a random order. I need him to sort it like that by default (on load) and not manually. Is there some sort of "on_load" commands like in the VB Editor ?

 

Or maybe it's because i have no key values so he tries to sort it using multiple fields ?

Are you setting the sort by property on the visual?

jdbuchanan71_0-1606150913329.png

 

jdbuchanan71
Super User
Super User

You can also make a copy of your date column and apply a custom format in the table design rather than in the measure.  Becasue it is still a date data type rather than a string from FORMAT it will sort correctly but display in the the format you want.

jdbuchanan71_0-1606146517102.png

 

DataZoe
Employee
Employee

Hi @Anonymous ,

 

On your new column you created to format your date, you can go to the ribbon and choose "Sort by Column" and pick the [Date_G] column. That should fix the sort order. If it gives you trouble, you may need to create = FORMAT([Date_G],"yyyyMMdd" column and then do the "Sort by Column" that instead. 

 

This link may help if I am not clear:
Sort by column in Power BI Desktop - Power BI | Microsoft Docs

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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