Forum Discussion

TinaB's avatar
TinaB
Regular Visitor
6 years ago

Date Time Hierarchy fails - Direct Query

I have a direct query connection to an oracle datasource, the event time stamp is a combined date/time and the normal date hierarchy does not work. I have the setting for import set to allow auto hie...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  TinaB 

For DirectQuery, there are some limitations for this model, there is No built-in date hierarchy in it

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

So you could create custom hierarchy in it.

and for different time in one date, there will be 1440 different time in minute dimension in one day, this calendar table will be so big.

and for create a relationship between calendar table  and fact table, usually,you need create a date column for datetime column

for example

DATE(YEAR('Table'[DateTime]),MONTH('Table'[DateTime]),DAY('Table'[DateTime]))
Then use this date column to create a relationship with calendar table, of course, you'd better create a time column too.

 

Regards,

Lin

TinaB's avatar
TinaB
Regular Visitor
6 years ago

I was hoping for a simple solution to the issue. I ended up limiting the charts by date slicer instead. I needed to get the reports published for folks to start using them. I will keep looking for an elegant solution. Thanks for trying.