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
KarlinOz
Advocate III
Advocate III

Default sort column of table in report

Ok so I have spent an hour searching the forum and reading, coming tantalisingly close to finding a solution but not getting there....

 

I have a table in my report thus:

 

Untitled.jpg

I wish to present the data sorted by the Date column. The first issue is that it is prefixed with the day and so sorting is by the first letter of the day name rather than the date. So, it would seem I need to sort my table by a cleanly formatted date column. That's no problem, I have a date column. So in the Data view I Select my date column with days, then on the Modelling tab I selece the "Sort By Column" icon and choose my date column to sort on:

2016-08-31 10_42_30-.png

 

But I get the error: "Sort By Another Column Error  We cannot sort the 'DateWDay' column by 'Date Cleand'. You can't have more than one value in 'Date Cleaned' for the same value in 'DateWDay'. Choose a different column for sorting or update the data in 'Date Cleaned'." I don't fully understand this because the DateWDay and Date Cleaned columns are derived from the same source (DateRaw) column. Can someone give me a clue as to how to progress this please. I presume that if I can get my data in the DateWDay column sorting by the Date Cleaned column then the table visualisation on the report should sort correctly by date.

 

Thanks,

Karl

 

 

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

I'm guessing that you "Date Cleaned" column is just the date and since your DateRaw column has date and time, then you technically have multiple values in DateRaw for a single entry in DateTime. This is just a guess because I don't see your DateCleaned column anywhere in your post. A few different ways to solve this, you could potentially translate your DateTime column into a new Duration column and then you should have a 1:1 mapping. A better way would probably be to cleanup your DateRaw column in your Power Query "M" code and let it come in as a pure Date or Date/Time column. Power BI will add all of the rest of info like weekday or you can use a simple DAX formula to get it.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

I entered some test data in your DateWDay format. I was able in the Power Query editor to change the column from Text to Date/Time and it switched it successfully so that is all you should need to do in order to get it to sort correctly.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

I'm guessing that you "Date Cleaned" column is just the date and since your DateRaw column has date and time, then you technically have multiple values in DateRaw for a single entry in DateTime. This is just a guess because I don't see your DateCleaned column anywhere in your post. A few different ways to solve this, you could potentially translate your DateTime column into a new Duration column and then you should have a 1:1 mapping. A better way would probably be to cleanup your DateRaw column in your Power Query "M" code and let it come in as a pure Date or Date/Time column. Power BI will add all of the rest of info like weekday or you can use a simple DAX formula to get it.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for your reply @Greg_Deckler, actually my Date Cleaned column is TimeDate:

 

2016-08-31 13_15_08-Experiment3 - Power BI Desktop.png 

 

and is just a DateTime formate of DateRaw column which is formatted as text in the source data. I'll take a look more closely at your suggestion later on. 

I entered some test data in your DateWDay format. I was able in the Power Query editor to change the column from Text to Date/Time and it switched it successfully so that is all you should need to do in order to get it to sort correctly.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler thanks for that observation. Now that I have created the column in the Data view rather than the query and changed the column type to Date/Time I can sort by date correctly with the day at the start. 

 

My next question is why does FORMAT([Date], "ddd, dd/mm/yyyy hh:mm") not result in AM/PM (12hr)? It outputs in 24hr format no matter whether I use hh:mm or HH:MM. But someone may have already asked that question so I'll dredge the forum first.

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.