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...
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
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