Sorting data label within three tiers of x axis on chart

[Roy_W]

Hi 

I create the chart with date and milestone information.

The X axis contains three tiers of labels, include "month", "milestone", and "year", as below.

But I found that I cannot sort month by order, I need the X axis like below:

Please helps me to find out if there is any method to acheive the requirement, thanks.

File download 

[selimovd · Accepted solution]

Hey @Roy_W ,

that is possible when you sort the "Column" by a sort by column.

Add to the table "Calendar" a new calculated column with the following code:

SortByColumn = 
    VAR __Date = [Date]
    VAR __Max = MAXX(FILTER('Calendar',[Date]<=__Date && NOT(ISBLANK([Milestone]))),[Date])
    VAR __Milestone = MAXX(FILTER('Calendar',[Date]=__Max),[Milestone])
VAR vColumn = IF(__Milestone <> "finish",__Milestone,IF(__Max = __Date,__Milestone,BLANK()))
RETURN
    SWITCH(
        vColumn,
        "m1", 1,
        "m2", 2,
        "m3", 3,
        "finish", 99, -- can also be 4, just wanted do let some space
        BLANK(), 100
    )

Afterwards mark the "Column" and set the sort by column:

Afterwards, the sort of the column will be like you wanted it to be:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Roy_W]

@selimovd 

Thanks, it worked.

But is there any other suggestion to deal the blank milestone?

Otherwise, I need to accurately select the right month contains first milestone.

Do you have any idea? thanks.

[selimovd]

Hey @Roy_W ,

I'm happy it worked.

Sorry, didn't get that. What do you mean with the blank milestones? How would you like to deal with them?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Roy_W]

Hi @selimovd ,

Sorry that I didn't mention clear.

The blank milestones mean that the values are out of milestone period.

As below, if I insert value before "m1", it will show wrong order of month.

Attached the file 

[selimovd]

Hey @Roy_W ,

and what do you want as result?

Should the blank values not show at all? Should they be at the beginning? Should they be at the end?

Best regards

Denis

[Roy_W]

Hi @selimovd 

Below is my expected result:

I am thinking if we can implement the dax formula for the milestone ordering;

the concept is to distinguish the order of blank milestones before "m1" and after "finish".

But I am not familiar with that, not sure if it is workable.

Regards,

Roy

[selimovd]

Hey @Roy_W ,

why don't you just sort by month? In this case it looks like you want that?!

If you have a value you can just assign one sort order, you cannot put "blank" at the beginning for the first year and at the end for the second year.

You could also not just say I want January as first month in even years and as last month in odd years. If you want to do something like this you have to do some workaround.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic