Forum Discussion

cy2452's avatar
cy2452
New Member
4 years ago

Sort by Column does not work

Hi ,

 

I am trying to sort the data by month in my visualization, and it is sorting alphabetically. So I created a column in my data table call sort and try to use the Sort by Column to sort it correctly, but it does not work. When I right click the clomn title and sort ascending, it works, but it will not help sort my visual.

 

My querys are linked by Post Month and Year Column

 

Please help. 

 

Thanks,

Chen

 

 

 

Need Help

4 Replies

  • cy2452's avatar
    cy2452
    New Member
    4 years ago

    No, that is a text field. I used Concat function in sql

    MAX(CONCAT(LEFT(DATENAME(MONTH,ARPB_TRANSACTIONS_PAYMENT.POST_DATE),3),' ',(RIGHT(YEAR(ARPB_TRANSACTIONS_PAYMENT.POST_DATE),2)))) AS 'POST_MONTH_AND_YEAR'

     

    • James-Harpin's avatar
      James-Harpin
      Resolver IV
      4 years ago

      cy2452 If you change it to a date field it should sort properly, you might have to use a Format function in your DAX

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    Hi cy2452 ,

     

    Here are two way to sort it:

    1. Create SORTING column in power query, then you can use sort by column:

    Add custom column and change data type:

     

    Then add SORTING column:

     

    Change the data type and remove custom column:

     

    Here is the M code:

    let

        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoUjAyUorViVbyTy5RMDIEM/3yy2BMx9J0GNM3Ea7WqzQPxnRLTYKLJsJFfRMrUdQaQpk5MKZLajKMGZxaAGbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [POST_MONTH_AND_YEAR = _t]),

        #"Added Custom" = Table.AddColumn(Source, "Custom", each "1 "&[POST_MONTH_AND_YEAR]),

        #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),

        #"Added Custom1" = Table.AddColumn(#"Changed Type", "SORTING", each Date.Year([Custom])*100+Date.Month([Custom])),

        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),

        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"SORTING", Int64.Type}})

    in

    #"Changed Type1"

     

    Now you can use sort by columns:

     

    Final output:

     

    1. Generate  SORTING column using DAX,

    Here is the DAX:

    SORTING =

    var _a = CONVERT("1 "&[POST_MONTH_AND_YEAR],DATETIME)

    return YEAR(_a)*100+MONTH(_a)

    Apply it in the tooltips:

     

    Note: If you want to sort in table visual, you need to add SORTING in the values field

    Output:

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.