Forum Discussion
How to sort Month in Dynamic Change Axis ? Sorting Month name
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")))
)
This idea of having to create new columns to sort is a cluge at best. I have PBIRS and I have no option to create new columns. As well the version of PowerBI is not honoring the Cube's sort. Can you all fix the underlying problem of not converting everything to strings and keep the data type, sort the data type appropriately...so no more hacks.
That would be helpful.
How will I sort the AxisValue column it has (QTR and Month value) in DimTable? Screenshot belowI need to sort this column AxisValue
That's the same question you asked in the initial post and the answer is the same as in the initial reply.
I did that not working if you can work on my pbix file it wil helpful so I can understand where I am doing wrong
Help me
- Any update it's bit urgent.
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 YuThanks 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")))
)
