Forum Discussion

tecumseh's avatar
tecumseh
Resolver III
1 year ago
Solved

Sort Month Single Letter Multiple Years

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"))
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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