Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Roy_W
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.

 

Roy_W_0-1629724636843.png

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

Roy_W_3-1629725451918.png

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

File download 

 

 

 

 

1 ACCEPTED SOLUTION
selimovd
Super User
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:

selimovd_0-1629728282764.png

 

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

selimovd_1-1629728324223.png

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
 

 

View solution in original post

7 REPLIES 7
selimovd
Super User
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:

selimovd_0-1629728282764.png

 

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

selimovd_1-1629728324223.png

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
 

 

@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.

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
 

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.

Roy_W_0-1629769886384.png

Attached the file 

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

Hi @selimovd 

Below is my expected result:

Roy_W_0-1629789438649.png

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

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
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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