topic Re: Time Intelligence with Direct Query in DAX Commands and Tips

Time Intelligence with Direct Query

TePe — Mon, 12 Aug 2019 13:55:06 GMT

Hi,

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.".

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.

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...

So what's the benefit of having a date table in this case (I know about the usual advantages)?

Bug or feature? ;-)

Thanks,

Re: Time Intelligence with Direct Query

Anonymous — Mon, 12 Aug 2019 14:53:15 GMT

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.

Best
Darek

Re: Time Intelligence with Direct Query

TePe — Mon, 12 Aug 2019 15:01:06 GMT

Darek,

thanks for you feedback. When I look at https://docs.microsoft.com/en-us/power-bi/desktop-date-tables 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...

Best Regards,

Thomas

Re: Time Intelligence with Direct Query

Anonymous — Mon, 12 Aug 2019 17:20:09 GMT

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

Re: Time Intelligence with Direct Query

TePe — Tue, 13 Aug 2019 19:57:55 GMT

HI,

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

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...

THANKS

Thomas

Re: Time Intelligence with Direct Query

Anonymous — Tue, 13 Aug 2019 20:21:57 GMT

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.

Best
Darek

Re: Time Intelligence with Direct Query

Anonymous — Tue, 13 Aug 2019 20:25:09 GMT

My guess is that quick measures just don't work (yet?) with DirectQuery.

When in doubt, go to documentation:

https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures#limitations-and-considerations

And now you know...

Best
Darek

Re: Time Intelligence with Direct Query

TePe — Tue, 13 Aug 2019 20:42:58 GMT

yep, might be the case that DirectQuery isn't supporting TimeIntelligence Quick Measures... The DAX formulas seam to work so it's just additional effort to build the measure manually...

Thanks,

Thomas

Re: Time Intelligence with Direct Query

Anonymous — Wed, 14 Aug 2019 08:58:55 GMT

The link I showed you in my previous post says clearly that quick measures do not work in DirectQuery. It's not "might be", it's 100%.

Best
Darek