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

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi,

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

     

     

     

     

    • tecumseh's avatar
      tecumseh
      Resolver III
      1 year ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

    • tecumseh's avatar
      tecumseh
      Resolver III
      1 year ago

      Thanks Anonymous ,

      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

    • UncleLewis's avatar
      UncleLewis
      Responsive Resident
      1 year ago

      Anonymous 

      The Rankx solution is returning a circular reference error.

      Any thoughts?

      Sample pbix is here

       

      thanks,

      -w

  • Anonymous's avatar
    Anonymous
    Not applicable
    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