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
AdamBoltryk
Resolver I
Resolver I

DATE format / sort date issue

Hi All,

 

Maybe this Community can help me with my problem 🙂

As you can see below, PBI sort days wrongly. From other hands it's sorted correctly as sortation order is 1..31 At this moments 01,02,03 etc are labels/text (as PBI do not allow me to set date format i wish DD), moved into 2nd DIM table and sorted 1...31 what is OK from arthemtic but wrong from calendar point of view.

How I can solve this problem? Any idea?

 

Capture.PNG

 

Adam

1 ACCEPTED SOLUTION

I just want share with all how I solve this "problem". I've been created new column with veeeeeeeeeeeeeery long input:

28_______________________________________________________28/01/2018

29_______________________________________________________29/01/2018

30_______________________________________________________30/01/2018

31_______________________________________________________31/01/2018

01_______________________________________________________01/02/2018

02_______________________________________________________02/02/2018

03_______________________________________________________03/02/2018

04_______________________________________________________04/02/2018

etc...

and now PBI automatically cut this fields showing only needed value:

Capture.PNG

It's not best/nice solution but it works 🙂

I am waiting for day I will be able to define my own date format, like "dd" 🙂

 

Regards.

View solution in original post

7 REPLIES 7
Stachu
Community Champion
Community Champion

you need to add numeric column with the sort order you require and use that for custom sort order in your text columnCapture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Maybe I did something wrong but I tried similar solution to this already. I set Rank column in my Calendar DIM and try sort my 01,02,03 texts byt this column. But PBI didn't allow me to do this as 'values in both columns have to be unique'... Obviously we have 01 for Jan, Mar, Apr etc. So I have extracted DIM table (called days) from calendar and I set sortation there 01..31. This works fine. Problem is when in same week I have 2 different months - ending one and starting next.

Capture.PNG

 

Any other ideas?

the granularity of the sorted column has to be the same - can you post your full calendar table and specify which column you've tried to sort by what?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Basically - in my data table I have only 1 column refere to date - formated as date:

Capture1.PNG

 

I have separate DIM_Calendar with a lots of columns. Index Colum (id_Date) it's excel date formated as integer. So in excel columns ix_Date and Date have same values just different format.

Capture2.PNG

 

In same table I have 2 columns - RANK and DAYNO. Rank is number, DayNo is text.

Capture2a.PNG

 

When I try sort DayNo by Rank I got obvious message:

Capture3.PNG

 

So I created sub-Dim table called Days and there I set sortation I expect.

Capture.PNG

 

But this works only if I am inside ONE month. When I use weeks and I have split months in week it not works as I need.

 

Capture4.PNG

 

Hi @AdamBoltryk

As tested, i can sort the table by "date" column, This can be achieved in query editor. then you can create in index column.

4.png

 

For each visual which adds "date" column in, you can sort by "date".

However,when you only add "dayno" and "week" in a visual, it can't be sort correctly, Because there are more than one value in "date" for the same value in "dayno".

5.png

So your method is a workaround someway.

 

Best Regards

Maggie

I just want share with all how I solve this "problem". I've been created new column with veeeeeeeeeeeeeery long input:

28_______________________________________________________28/01/2018

29_______________________________________________________29/01/2018

30_______________________________________________________30/01/2018

31_______________________________________________________31/01/2018

01_______________________________________________________01/02/2018

02_______________________________________________________02/02/2018

03_______________________________________________________03/02/2018

04_______________________________________________________04/02/2018

etc...

and now PBI automatically cut this fields showing only needed value:

Capture.PNG

It's not best/nice solution but it works 🙂

I am waiting for day I will be able to define my own date format, like "dd" 🙂

 

Regards.

the message is not related to same day numbers across different months, it's pointing that for some DayNo there are multiple different Ranks, e.g. DAyNo 09 has Rank 9 and null, etc.
you mention that you have weeks as well - if what you try to sort is day + week the sorting column will need to be in exactly same granularity

EDIT - I see it in your 3rd screenshot - you sort day ranging 01-31 by Rank based on Date, which has values like 457-459, which is wrong. You need a separate rank field ranging 1-31



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.