Date Time Hierarchy fails - Direct Query

[TinaB]

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 hierarchy. I have attempted to create a relational table for date to allow use of montly charts, but when I use the Month-Yr, my chart change from actual count to single result. The calendar function only lists the 12:00 time for each of the dates in my table, but the records in the oracle table have varible time stamps from 24 hour reporting. I have searched for DAX syntax and other help tools, but have not been able to resolve. 

Is there a way to replace the date column in my relational table with more than just one entry per day? I think the issue is that the relationship between the date in the heirarchy table and the actual records is not treating all the data for the day as multiple events instead is only treating it as one event for over 200 records.

Has anyone else ran into this issue and found a solution? Any help would be greatly appreciated.

[v-lili6-msft]

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]

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.