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")))
    )

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

Hi mdaamirkhan,

 

As the Month Period column is a TEXT type column, it will be sorted based on the first character of Month Period values automatically in a matrix visual. 

 

In your scenario, to work around the issue, you can create a calculated column below: 

 

DateNew = SWITCH(LEFT(DimTable[Month Period],3),"Jan",DATE( RIGHT(DimTable[Month Period],4),1,1),"Feb",Date( RIGHT(DimTable[Month Period],4),2,1),"Mar",DATE(RIGHT(DimTable[Month Period],4),3,1),"Apr",DATE(RIGHT(DimTable[Month Period],4),4,1),"May",DATE(RIGHT(DimTable[Month Period],4),5,1),"Jun",DATE(RIGHT(DimTable[Month Period],4),6,1),"Jul",DATE(RIGHT(DimTable[Month Period],4),7,1),"Aug",DATE( RIGHT(DimTable[Month Period],4),8,1),"Sep",DATE(RIGHT(DimTable[Month Period],4),9,1),"Oct",DATE(RIGHT(DimTable[Month Period],4),10,1),"Nov",DATE( RIGHT(DimTable[Month Period],4),11,1),"Dec",DATE(RIGHT(DimTable[Month Period],4),12,1))

 

Then replace Month Period as this new column DateNew in matrix Columns section, enable Date hierarchy and only keep Year and Month. 

 

 

Update: Please use dummy data in PBIX file to protect your sensitive infomration. 

 

Best Regards,
Qiuyun Yu

mdaamirkhan's avatar
mdaamirkhan
Post Prodigy
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")))
)