Forum Discussion
Anonymous
5 years agoNot applicable
Direct Query - clarification on time intelligence
Hello Community - Direct Query seems quite limited except for any very nuanced use cases. Aside from the other limitations, one thing I am trying to clarify is the ability to use time intelligence features.
I have a data set that includes a date table and time intelligence seems to work. Does that make sense? I've also tried adding a date table to a direct query data model (that did not inherently contain a date table), and time intelligence did not work.
- No built-in date hierarchy: When importing data, every date/datetime column will also have a built-in date hierarchy available by default. For example, if importing a table of sales orders including a column OrderDate, then upon using OrderDate in a visual, it will be possible to choose the appropriate level (year, month, day) to use. This built-in date hierarchy isn't available when using DirectQuery. If there's a Date table available in the underlying source, as is common in many data warehouses, then the DAX Time Intelligence functions can be used as normal.
1 Reply
- lbendlinSuper User
"and time intelligence did not work. " - how did that manifest itself?
You don't really want to use the built-in date hierarchy. Having a dedicated calendar table (and marking it as such) is the recommended approach.