Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

sorting by month (text)

ssas tabular model - I have a month column, which is a text datatype, and is based on a day column which is date datatype   I have a power bi report doing live connection to this model, when I try ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Anonymous Your calculation should be:

    =YEAR([xxx Day]) * 100 + MONTH([xxx Day])

     

    Set this as your Sort By column of your text month name.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Greg_Deckler  Why multiply * 100

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous See previous reply.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Greg_Deckler  so I added a new column with above calculation in the msbi tabular model

     

    year(day_column) * 100 + month

     

    and data format General, data type whole number, the power pivot shows correct values but power bi sums it up and shows wrong values 

     

     

    • v-jingzhang's avatar
      v-jingzhang
      Community Support
      4 years ago

      Hi Anonymous 

       

      This thread may be helpful: https://stackoverflow.com/questions/70458737/sort-a-calculated-column-ssas-tabular-model . After creating the new whole number data type column in the tabular model, select the original text data type Month column and set the Sort By Column property to the new whole number data type column. 

       

      Best Regards,
      Community Support Team _ Jing
      If this post helps, please Accept it as Solution to help other members find it.