Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |