Forum Discussion

Winniethewinner's avatar
Winniethewinner
Icon for Helper IV rankHelper IV
4 years ago
Solved

Month Order for a calculated dimension table

Hi Everyone, 

I created a calculated dimension table and use it to dynamically switch the axis in the chart. Here is the calculated table DAX:

And here is the chart, which is switched based on the axis on the left:

 

The question I have is, the months are 7-month forwarding, i.e. I will have 07,08,09,10,11,12,01 as the next, how can I sort it properly for those months? 

(because I use the calculated table for "Month" attribute, those values 07,09,09,10,11,12,01 are in text format)

  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi  Winniethewinner 

    Thanks for reaching out to us.

    >>The question I have is, the months are 7-month forwarding, i.e. I will have 07,08,09,10,11,12,01 as the next

    Because they are in text form, they cannot be sorted by themselves, so you can try sort by column. Sort one column by another column in Power BI - Power BI | Microsoft Docs

    Or you can try this one, use year-month column, for example,

     

    Best Regards,

    Community Support Team _Tang

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

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi  Winniethewinner 

    Thanks for reaching out to us.

    >>The question I have is, the months are 7-month forwarding, i.e. I will have 07,08,09,10,11,12,01 as the next

    Because they are in text form, they cannot be sorted by themselves, so you can try sort by column. Sort one column by another column in Power BI - Power BI | Microsoft Docs

    Or you can try this one, use year-month column, for example,

     

    Best Regards,

    Community Support Team _Tang

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

    • Winniethewinner's avatar
      Winniethewinner
      Icon for Helper IV rankHelper IV
      4 years ago

      Thanks v-xiaotang . I'm uploading this sample data - I'd like to sort the month properly when switching to month dimension.  if I create a separate Month_table, how do I link it to the DimTable which also have other dimensions and values? Another thing is when I add relationship using Index between 2x tables, the autosum (sigma) disappears, any idea why? Thanks in advanced.