Sort columns in matrix when connected to SSAS (Live Connection)

[Omega]

Hi, 

I am connected to SSAS (Live Connection) and created a matrix that has date as column. I want to show the latest date from left to right. I've read multiple threads but seems there is no solution. 

Has anyone figured a solution to do it at report level instead of SSAS level?

[jbarta_mlchc]

I was struggling with this as well with a live connection to SSAS and this thread helped me think about it in a different way and it worked. So thanks to everyone that posted. 

I first tried to do a dense_rank in the SQL statement for the SSAS table in the model and used that as the sort by in the SSAS for my date column, but that didn't seem to work. 

I then duplicated my date column in the SQL query and used that column to create a calculated column in SSAS using RankX (=RANKX(HistoricalOBCaseLoad,HistoricalOBCaseLoad[RunDateOrderColumn])). Then I used this calculated column as the sort by for my date column. That seemed to work and now my matrix and my chart show the most recent date on the left side. A calculated column might not be the best option if your data set is really big but mine is relatively small. 

Thanks again for this thread!

[v-alq-msft]

Hi, @Omega 

Based on my research, It is unsupported to achieve your requirement. When connecting live, no data from the tabular model is imported into Power BI Desktop. Each time you interact with a visualization, Power BI Desktop queries the tabular model and calculates the results you see. You’re always looking at the latest data that is available in the tabular model. You can create report level measures in the Power BI Desktop, but all other query and modeling features are disabled when exploring live data.

For further information, you may refer to the official document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-alq-msft 

This option is not available for any other sources. 

For simple sorting, i don't think creating index columns is right approch.

I think microsoft team need to add this simple feature for matrix.

Please vote for similar ideas. I already  mentioned link.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

I don't think  there is direct option to show descending order sorting of Column .

Bu default it shows ascending order for columns.

Else you need to do some trick like this.

https://community.powerbi.com/t5/Desktop/Matrix-Column-Head-Order/td-p/71572

Note : you need to add index column  at SSAS side only.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

You can vote for similar ideas.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36945946-add-sort-on-a-column-fea...