Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
apmulhearn
Helper III
Helper III

Help with Date/Time + Text Concatenate Sort in Chart Visualization - out of order due to 1 in month

Hi,

 

Below is a chart visualization and the issue I'm trying to fix.
"New Subject" is a combination of Year/Month and a subject line.

I want the ones highlighted in yellow to be at the top when sorted in descending order, as 2022-11 is more recent than 2022-3. But the 1s are distorting their position. 


The syntax for creating the column is: 

NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject]

 

apmulhearn_0-1668625874506.png

 

1 ACCEPTED SOLUTION

@apmulhearn ,

The SWITCH function is structured like this:

SWITCH(
         TRUE(),
          Condition1, Result1,

          Condition2, Result2,

          Else all other conditions.

https://learn.microsoft.com/en-us/dax/switch-function-dax

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

New Subject = SWITCH(
                TRUE(),
                month('Top 300 and Virt Stats'[Sent At (Your time zone)])< 10, 
// This is first condition - if Month is less than 10, then you note I add the "-0" in your result

year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-0" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],
// This is the result if condition is true.

year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject])
// This is the result if no conditions are met.  That is if Month >= 10, then just use "-"

The "//" indicates a comment, just for explanation purposes.

View solution in original post

3 REPLIES 3
rsbin
Super User
Super User

@apmulhearn ,

What you need to do when creating Year-Month is to ensure they are the same number of characters.

If the Month is less than 10, add a "0" into your String.  This will then enable you to sort Year-Month properly.

Year-Month = SWITCH(
                TRUE(),
                month('Top 300 and Virt Stats'[Sent At (Your time zone))< 10, 
NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-0" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],
NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],

If the Month is less than 10, add a "0" into your String.  This will then enable you to sort Year-Month properly.

Regards,

Hi! This makes sense conceptually for sure but I'm missing something in the syntax. Can you help me?

apmulhearn_0-1668704918731.png


This is what is in my original NewSubject column:

NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject]

And this is what I put in the new column, which is resulting in an error.
Year-Month = SWITCH(
                TRUE(),
                month('Top 300 and Virt Stats'[Sent At (Your time zone)])< 10,
NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-0" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],
NewSubject = year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject])

@apmulhearn ,

The SWITCH function is structured like this:

SWITCH(
         TRUE(),
          Condition1, Result1,

          Condition2, Result2,

          Else all other conditions.

https://learn.microsoft.com/en-us/dax/switch-function-dax

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

New Subject = SWITCH(
                TRUE(),
                month('Top 300 and Virt Stats'[Sent At (Your time zone)])< 10, 
// This is first condition - if Month is less than 10, then you note I add the "-0" in your result

year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-0" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject],
// This is the result if condition is true.

year('Top 300 and Virt Stats'[Sent At (Your time zone)]) & "-" & month('Top 300 and Virt Stats'[Sent At (Your time zone)]) & ": " & 'Top 300 and Virt Stats'[Subject])
// This is the result if no conditions are met.  That is if Month >= 10, then just use "-"

The "//" indicates a comment, just for explanation purposes.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.