Matrix order columns - SSAS Azure DWH

[Yme]

Dear readers,

I have a small problem I cannot figure out. I have a matrix where I want the columns sorted based on a invisible datetime column.

The rows are years: and are sorted. Good!

The columns are monthnames: MMM, but are not sorted. I want these columns names being sorted by a corresponding datetime column (invisible).

I searched on the internet and mostly I get solutions referring to the model creation pane in the menu and sort there on a specific column. However this option is greyed out, because I am working with SSAS azure DWH. 

I also tried to sort the table on the month datetime, but still the columns ignore this sort.

Now my monthnames are messed up in the columns. I even tried to add a number, like 1,2,3, but then still the columns are string sorted which means: 1, 10, 11, 12, 2, 3, 4 etc.

Can someone provide me with a solution to this problem?

Best regards.

[v-jiascu-msft]

Hi @Yme,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Yme]

Hi @v-jiascu-msft,

Yes, done. After some changes to the data model I used your solution and it works! The months are now sorted in the columns.

Thank you!

Best regards

[v-jiascu-msft]

Hi @Yme,

Is it a live connection? If so, I'm afraid you have to adjust it in the SSAS model. If not, you could try these methods below.

1. If you add numbers like 1, 2, 3, you still need to change its type to "Whole Number" or other numeric types.

2. Add a numeric type column and use the function "Sort by Column".

Best Regards,
Dale

[Yme]

Hi @v-jiascu-msft,

Yes, it is indeed a live connection. Unfortunatly I have to search for a solution in the SSAS model. One solution I now can think of is changing the column values to: a. Jan 18, b. Feb 18, c. Mar 18 etc.

It would however be a nice feature for the Powerbi Matrix also to sort on the columns with a live connection. I mean it is possible to sort the rows, so why not the columns.

Best regards,

[v-jiascu-msft · Accepted solution]

Hi @Yme,

Since it's a live connection, almost all are defined in the SSAS Server. Maybe you can change it in the SSAS model. Please refer to the snapshot below. It could help.

Best Regards,
Dale

[Yme]

Hi @v-jiascu-msft,

I will take a look at your solution. See if I can this one to my datamodel.

I let you know if it worked!

Thanks in advance 🙂

Best regards.