Forum Discussion

TePe's avatar
TePe
Helper IV
7 years ago

Time Intelligence with Direct Query

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.mi...
Anonymous's avatar
Anonymous
Not applicable
7 years ago
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's avatar
TePe
Helper IV
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    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
    • TePe's avatar
      TePe
      Helper IV
      7 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        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