topic Re: Custom Sort Order for a Measure in DAX Commands and Tips

Custom Sort Order for a Measure

Anonymous — Wed, 17 May 2023 15:07:15 GMT

I have the following measure which returns the screenshot of data below when applied to a matrix - essentially the date with a little calculation of how many days old the note is.

StatusNoteDateFormatted = VAR DaysOld = CALCULATE( DATEDIFF( SELECTEDVALUE('#Success - Project List'[ProjectStatusNoteDate]), TODAY(), DAY ) ) RETURN IF( DaysOld = BLANK(), BLANK(), SELECTEDVALUE('#Success - Project List'[ProjectStatusNoteDate]) & " (" & DaysOld & " Days Old)" )

When you click the matrix header to sort chronologically, it sorts numerically rather than by the date.

i.e.
5/9

5/8

5/5

5/3

5/12

5/11

I would like it to sort chronologically, oldest to newest or vice versa.

I found this article which shows how to introduce a custom sort order to a measure but they only have 3 specific categories wheras my measure is dynamic based on the value of Status Note Date. Does anyone have any other methods similar where I could make this measure sort by date (or even by the count of days old) rather than numeric order when the column header is clicked?

Thank you in advance.

Re: Custom Sort Order for a Measure

amitchandak — Wed, 17 May 2023 15:14:57 GMT

Anonymous , Mark the datetime column (or take out from this column) and mark that sort column for Status Not Date

Re: Custom Sort Order for a Measure

Anonymous — Wed, 17 May 2023 17:11:22 GMT

Hey amitchandak, thanks for your reply.

Unfortunately Status Note Date Formatted is a measure which is the column you see in the visual (Status Note Date). I was under the impression you can't define a column to sort a measure by.

Re: Custom Sort Order for a Measure

Anonymous — Thu, 18 May 2023 18:46:05 GMT

amitchandak I converted my measure to a calculated column so that I could use the method you described. I'm still having a problem though. In data view, you can see that the calculated column is being sorted by ProjectStatusNoteDate (which is marked as a date field) and the sort is in the correct order.

However, when I go to my visual it is still out of order. In descending order, we see 5/5, 5/3, 5/2, then 5/11, 5/10, 4/29 and so on.

I've tried resorting the visual many times and continue to get the same result. Is there anything else I can try?

Re: Custom Sort Order for a Measure

ppm1 — Thu, 18 May 2023 22:33:08 GMT

It is sorting alphabetically (descending). You can FORMAT your date as "MM/dd/yyyy" so that the zeros show up and you get the needed order.

Pat

Re: Custom Sort Order for a Measure

Anonymous — Fri, 19 May 2023 16:21:36 GMT

Hi ppm1 thanks for your response. So the sort by column (StatusNoteDate) I changed the date format to "MM/dd/yyyy". I can't format the calculated column (StatusNoteDate&DaysOld) that way since I am adding a string, it can't be both date and string. But if I sort the Calculated column by StatusNoteDate, it sorts in the correct order in data view but is still going alphabetical in the visual.

Re: Custom Sort Order for a Measure

ppm1 — Fri, 19 May 2023 16:31:41 GMT

I meant you can FORMAT the date in your measure.

FORMAT(SELECTEDVALUE(...), "yyyy/MM/dd")

Pat

Re: Custom Sort Order for a Measure

Anonymous — Mon, 22 May 2023 15:20:03 GMT

Oh I understand now, thank you. This works!!