Forum Discussion
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:
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.
3 Replies
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?
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])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.
