cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sorting data label within three tiers of x axis on chart

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:

1 ACCEPTED SOLUTION
Super User

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

7 REPLIES 7
Super User

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

Frequent Visitor

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.

Super User

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

Frequent Visitor

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.

Super User

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

Frequent Visitor

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

Super User

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

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors