Forum Discussion
Order axis for matrix visual
Hi community,
I need help with the following, is there a way I can sort the column axis (dates) from oldest to newest but from left to right? It doesn't let me do it, I have a client that doesn't want to scroll right until the most recent week and it makes sense
Thanks
5 Replies
Hi krist_pbi ,
I tested that myself. There is no option to sort the column in a matrix unless it is sorted by another column. So the workaround is to create a date calc column in DAX or duplicate it in PQ and then use RANKX to create a sort by column.
See sample formulas and the screenshot below
Date2 = 'DataTable'[Date]DateRank = RANKX ( VALUES ( 'DataTable'[Date] ), 'DataTable'[Date],, DESC )you will do need the extra colum, you can create a column as year&month to use that as sort column for the main dat ecolumn and so it will work around it also you can try creating a calendar using a tool bravo its pretty good and efficient for createing this type of dimensional tables and other stuff:
Thank you, just one question, the formula you shared then has to be created as a calculated column in my Calendar table, correct? Then just sort the weeks by this column, correct?
DateRank = RANKX ( VALUES ( 'DataTable'[Date] ), 'DataTable'[Date],, DESC )
you need to sort the column in the data table itself first, the visuals take the order set upon the table first by default, theres a pane on the top of power bi desktop that lets you sort a column by givin condition or another column
Hi, you can create an extra column to rankx your date in order of Asscending
extra column = RANKX(your table, date column,,1)
then you sort your date colum by extra column, an the matrix will show expected result.
