Sort by date and time

[HansBaeten]

Hello,

I'm having a datamodel in a SSAS tabular cube with a lot of timeseries data.

Timeseries are in datetime format. In order to optimize the data model size, I had to split the datetime field in 2 seperate fields: date and time.

The end user is working with a lot of table visuals where he/she wants to order the data by the datetime field. In Power BI I can't order a table by 2 columns. The sort by column is disabled as I'm connected to a SSAS tabular cube.

In SSAS I can specify a column to order a specific column. But I can't order my date column by the time column, as this column doesn't contain unique values.

Does anyone can advice me how to sort a table, based on date and time values, without having a single datetime column in my datamodel?

Thanks for the help.

Regards,

Hans 

[v-lili6-msft · Accepted solution]

hi @HansBaeten 

For your datasource is SSAS, and you are use live connection.

So you could only create measure in this report in power bi.

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#live-connections

For your case, you refer to these two ways:

1. in SSAS tabular cube, create a datetime field that combine date column and time column, then set sort by column in SSAS tabular cube.

"Timeseries are in datetime format. In order to optimize the data model size, I had to split the datetime field in 2 seperate fields: date and time."

2. Create a measure that that combine date column and time column, and then drag it into visual, then sort by this visual.

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#selecting-which-column-to-use-for-s...

Regards,

Lin

[mwegener]

Hi @HansBaeten ,

look at this.

https://community.powerbi.com/t5/Desktop/Sorting-by-multiple-columns/m-p/536884#M251918

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.