Time Intelligence with Direct Query

Question

Hi,&nbsp;I know about the limitations of Time Intelligence when using Direct Query (i.e. you don't see Time Intelligence in the Quick Measures). However digging into the documentation (https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about) I found this sidenote: "Note however that if there is 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.".&nbsp;I tried that with a Azure SQL DWH and the AdventureWorks DW sample db (you need to delete 2006 dates from dimDate to make it working). Functions like DATESYTD seem to work fine however the Time Intelligence related Quick Measures are still not available.&nbsp;Anyway I tried the same with a view on the DWH. So I joined the facts with the dimDate (so now I don't have a separate date table anymore) and it still works?!?! And Time Intelligence features are still missing in the Quick Measures... &nbsp;So what's the benefit of having a date table in this case (I know about the usual advantages)?&nbsp;Bug or feature? ;-)&nbsp;Thanks,

anonymous · Answer

Mate, your DW Date table has to follow exactly the same rules as the Date table in PBI has to. If that's not the case, forget about any time-intel calculations.BestDarek

tepe · Answer

Darek,&nbsp;thanks for you feedback. When I look at&nbsp;https://docs.microsoft.com/en-us/power-bi/desktop-date-tables&nbsp;I only see that I need to mark my DimDate as the date table (the scenario I talked about when I had separate date and fact tables). That's what I did, the assistant is so nice to check field setting and data quality in the date table on its own... Still no Time Intelligence in Quick Measures and still DATESYTD works...&nbsp;Best Regards,&nbsp;&nbsp;Thomas

anonymous · Answer

Is you Date table marked as such in PBI? Does it join to the fact table on the right field in the model? Does it cover all the years in full that are present in the columns to which it's joined? If the conditions are satisfied, then you should have no problem with the time-intel functions.

Best
Darek

tepe · Answer

HI,&nbsp;so what I did:- created an Azure Data Warehouse with AdventureWorks DW sample DB- deleted 2006 data from dimDate because there's a gap in the date table, Power BI isn't happy with that. There's no sales data for 2006...- created a DirectQuery model with Power BI latest version- selected DimDate and FactInternetSales- built up a relationship between FactInternetSales.OrderDateKey and DimDate.DateKey- I marked DimDate as Date Table with FullDateAlternateKey as the date field- Tried to create a Quick Measure with Time Intelligence but the corresponding section isn't available in the calculation list&nbsp;What works is DAX formulas like DATESYTD() but this is independent of using a separate date table or not. So still I'm confused what extra functionality should be enabled with a date table in the DirectQuery source compared to anything else...&nbsp;THANKS&nbsp;&nbsp;Thomas

anonymous · Answer

Time-intel functions ALWAYS rely on a separate Date table that stores contiguous dates for whole years. This is how it works. If you use them on something that is not a Date table, you'll get rubbish.BestDarek

Forum Discussion

Time Intelligence with Direct Query

8 Replies

Related content

merging direct query queries

RLS over Direct Query

Direct Query issue with Snowflake

Direct Query and Time Intelligence functions

MySQL Connector - Direct Query