Forum Discussion

mdaamirkhan's avatar
mdaamirkhan
Post Prodigy
8 years ago
Solved

How to sort Month in Dynamic Change Axis ? Sorting Month name

Hello All,   I need urgent help on How to sort Month in Dynamic Change Axis ? Sorting by Month name like(Jan 2017 , Feb 2017 and so on). I have share file pbix link: https://drive.google.com/open?i...
General Comment
Need Help
Show and Tell
  • mdaamirkhan's avatar
    mdaamirkhan
    8 years ago

    Thanks a lot for helping me but For Dynamic change Axis Month to QTR / QTR to Month switch with sorting by Month name I have wrote below dax query:

     

    DimTable =
    ADDCOLUMNS(
    UNION ( CROSSJOIN(DISTINCT('Month'[Month Period]), DATATABLE("AxisDimension", STRING, { {"Month"}}), DISTINCT(Quarter[Quarter Period])),
    CROSSJOIN(DISTINCT('Month'[Month Period]),DATATABLE("AxisDimension", STRING,{ {"QTR"}}), DISTINCT('Quarter'[Quarter Period])))
    ,"AxisValue", SWITCH([AxisDimension], "QTR", FORMAT(Quarter[Quarter Period], "0000"),"Month", 'Month'[Month Period])
    ,"AxisSort", SWITCH([AxisDimension], "QTR", CONCATENATE( RIGHT(Quarter[Quarter Period],4), CONCATENATE("0", RIGHT(LEFT(Quarter[Quarter Period],2),1))) ,"Month", CONCATENATE( RIGHT('Month'[Month Period],4), SWITCH(LEFT('Month'[Month Period],3), "Jan", "01", "Feb", "02", "Mar", "03", "Apr", "04", "May", "05", "Jun", "06", "Jul", "07", "Aug", "08", "Sep", "09", "Oct", "10", "Nov", "11", "Dec", "12")))
    )

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi mdaamirkhan,

 

I just looked at your PBIX file

 

 

I think you can just make a column next to it that goes from 1-36 after that you can sort on that column that should probably give what you want.

 

Regards,

L.Meijdam

mdaamirkhan's avatar
mdaamirkhan
Post Prodigy
8 years ago

Can you please please help me on this on my pbix file it will be great 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi mdaamirkhan,

     

    If you add a column like this:

    You can configure your visual like this:

    If  you follow these 2 steps it should work fine in your situation, I believe you can do that yourself.

     

    Regards,

    L.Meijdam

    • mdaamirkhan's avatar
      mdaamirkhan
      Post Prodigy
      8 years ago

      can you please share the file with this please and how you create a ID

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi mdaamirkhan,

         

        I made the ID by hand in excel and used the option "Enter data" where I just pasted the below data. If you paste it the "Month" and "ID" will automatically be promoted to column headers.

         

        MonthID
        Jan-152015-01
        Feb-152015-02
        Mar-152015-03
        Apr-152015-04
        May-152015-05
        Jun-152015-06
        Jul-152015-07
        Aug-152015-08
        Sep-152015-09
        Oct-152015-10
        Nov-152015-11
        Dec-152015-12
        Jan-162016-01
        Feb-162016-02
        Mar-162016-03
        Apr-162016-04
        May-162016-05
        Jun-162016-06
        Jul-162016-07
        Aug-162016-08
        Sep-162016-09
        Oct-162016-10
        Nov-162016-11
        Dec-162016-12
        Jan-172017-01
        Feb-172017-02
        Mar-172017-03
        Apr-172017-04
        May-172017-05
        Jun-172017-06
        Jul-172017-07
        Aug-172017-08
        Sep-172017-09
        Oct-172017-10
        Nov-172017-11
        Dec-172017-12

        (it is important that the column "ID" is in Text format not in Date format !) 

         

        Regards,

        L.Meijdam