Sort Month Single Letter Multiple Years

[tecumseh]

Hi all,

Using PBID Oct, 2024.

I created a Calc Column in my Date Table for the Initial of the month name and made it unique using UNICHAR(8203) below
I'm trying to sort this columns but I keep getting "More than 1 value in the Sort Column for the Name Column.
Ideas what I'm missing?
Thanks,

w

DAX

Month Name Initial =

VAR __Charcter = UNICHAR(8203)

VAR __CharCount = 'Date Table'[MonthNumber] + 'Date Table'[Year]

VAR __MonthLabel = LEFT('Date Table'[MonthName],1)

RETURN

CONCATENATE(

    __MonthLabel,

    REPT(__Charcter,__CharCount)

)

Sort Year Month = VALUE(YEAR('Date Table'[Date]) & FORMAT(MONTH('Date Table'[Date]),"00"))

[v-xiaocliu-msft · Accepted solution]

Hi  @tecumseh  , 

Unfortunately, we can’t seem to hide columns in a bar chart like we can hide columns in a table.

As a workaround, please try to create a rank column. The ranking value is determined by the [year Month] column.

This method can be used to reduce the number of zero-width characters to avoid errors during sorting.

Column = RANKX(VALUES('Table'[Sort Year Month]),[Sort Year Month],,ASC)

Month Name Initial =

VAR __Charcter = UNICHAR(8203)

//VAR __CharCount = 'Table'[MonthNumber] + 'Table'[Year]

VAR __MonthLabel = LEFT('Table'[MonthName],1)

RETURN

CONCATENATE(

    __MonthLabel,

    REPT(__Charcter,[Column])

)

Best Regards,

Wearsky

[v-xiaocliu-msft]

Hi @tecumseh ,

After further research, I think it's the zero width space string is too long causing the sort to fail.

As a workaround, please try to sort in visula rather than sort in table. And then you can hide the column.

Best Regards,

Wearsky

[UncleLewis]

@v-xiaocliu-msft 

The Rankx solution is returning a circular reference error.

Any thoughts?

Sample pbix is here

thanks,

-w

[tecumseh]

Thanks @v-xiaocliu-msft ,

I'm using a Clustered Column Chart
I sorted the axis on your solution so that looks great
But I cant figure out how to hide the Sort Year Month column on the x axis?



Thanks,
-w

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

[tecumseh]

Thanks @Jihwan_Kim ,

Add the year to your matrix and you will see same issue I am having getting proper sort.
You'll see
J 2023
J 2024
F 2023
F 2024

I need to show J - D 2023 followed by J - D 2024.
Thanks
-w