Forum Discussion
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?id=0BxreilNC4Avsb0dVS3pqQU9HODA .If you can help on this on my pbix and share with me it will be very helpful.
cannot sort Month
Thanks
Aamir
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")))
)
22 Replies
Hi mdaamirkhan,
In this article it is clearly explained, they also attached a PBIX file.
https://blogs.msdn.microsoft.com/samlester/2017/04/02/sorting-by-month-names-in-power-bi/
Regards,
L.Meijdam
Hi,
I have messed up if you kindly create on my pbix file which I have shared it will be helpful
Hi mdaamirkhan,
I typed this message on my mobile phone so I am affraid I can't do that, my advice is to create a new PBIX and try to get it to work there following the steps from the article. If you can make it work with some sample data in a new PBIX you can do it to your own more advanced PBIX too with that knowledge.
Plus the added value is that after this you yourself knows how to do it, you won't achieve that if you let someone else do it for you.
Regards,
L.Meijdam
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.
