Ordering matrix columns in live query connection setup

[gabrielefugazzi]

Hi everyone,

I'm recently struggling with a customer request: he asked me to order the following matrix

re-ordering the column year values (2019, 2020)  in descending order (2020, 2019).

It seems that live connections does not enable the delopers to use the ordering columns functionalities.

How could I order matrix columns if I'm working on a live query connection?

[v-diye-msft · Accepted solution]

Hi @gabrielefugazzi 

You need to add the index column in SSAS to make the table similar as below:

Then click the Year column to sort by column1 (But note that you need find the option similar in SSAS)

I found official references could probably provide you more hints:

https://docs.microsoft.com/en-us/analysis-services/tabular-models/add-columns-to-a-table-ssas-tabula...

https://docs.microsoft.com/en-us/analysis-services/tabular-models/sort-data-in-a-table-ssas-tabular?...

[amitchandak]

@gabrielefugazzi , the live connection you have set an order by column in SSAS.

This how we do. We create a desc rank on year and mark that as sort column. That will sort recent year as first

[FarhanAhmed]

Do it at source Level ...

I believe your Live connection is of SSAS cube.

you need to create a Sort Column in your data, do changes accordingly in Dimension Field property.

Change the orderby property to Key

[gabrielefugazzi]

Thank you for your answer. On the back I have a tabular model. Since it's my first time working with this tools have you the experience to tell me how to do it in tabular?

My fields proprierties in visual studio are like this

Thank you very much. 

[FarhanAhmed · Accepted solution]

I think field "Ordina per colonna" is the same field as "Sort by Other Column".

You can use this property to sort values by other fields

[v-diye-msft · Accepted solution]

Hi @gabrielefugazzi 

You need to add the index column in SSAS to make the table similar as below:

Then click the Year column to sort by column1 (But note that you need find the option similar in SSAS)

I found official references could probably provide you more hints:

https://docs.microsoft.com/en-us/analysis-services/tabular-models/add-columns-to-a-table-ssas-tabula...

https://docs.microsoft.com/en-us/analysis-services/tabular-models/sort-data-in-a-table-ssas-tabular?...