Forum Discussion
Sort by date and time
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
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.
Regards,
Lin
2 Replies
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.
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.
Regards,
Lin
