cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Direct Query and Time Intelligence functions

Hi,

 

Power BI has very powerful Time Intelligence capabilities.  

 

However, they seem not to be applicable when using a direct query.

 

Am I missing something big?

 

What is the alternative to the Time Intelligence functions when using Direct Query?

 

Thank you

Tamir

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Direct Query and Time Intelligence functions

Hi Tamir, 

 

The time intelligence takes a toll when connecting to direct query. 

If you are expecting the date hierarchy in specific , you would have to create it for yourself by making columns [year], [quarter],[month],[date] and then use them to create a hierarchy.

 

You can get the modelling and reporting limitaions for direct query connection in 

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

 

Regards,

Praisely

View solution in original post

Highlighted
Community Support
Community Support

Re: Direct Query and Time Intelligence functions

Hi Tamir,

 

Yes, in direct query mode, time intelligence function are not available, please refer to the documentation: https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d.... The alternative ways depend on your specific senario, so if you want to achieve some result using time intelligence, please share sample data and expected result.

 

Regards,

Jimmy Tao

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Direct Query and Time Intelligence functions

Hey Tamir,

 

basically the build-in Time Intelligence functions are syntax sugar for a combination of other DAX functions.

 

Here you will find a lot of examples how to rewrite these build-in functions:

https://www.daxpatterns.com/time-patterns/

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
Highlighted
Resolver II
Resolver II

Re: Direct Query and Time Intelligence functions

Hi Tamir, 

 

The time intelligence takes a toll when connecting to direct query. 

If you are expecting the date hierarchy in specific , you would have to create it for yourself by making columns [year], [quarter],[month],[date] and then use them to create a hierarchy.

 

You can get the modelling and reporting limitaions for direct query connection in 

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

 

Regards,

Praisely

View solution in original post

Highlighted
Community Support
Community Support

Re: Direct Query and Time Intelligence functions

Hi Tamir,

 

Yes, in direct query mode, time intelligence function are not available, please refer to the documentation: https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d.... The alternative ways depend on your specific senario, so if you want to achieve some result using time intelligence, please share sample data and expected result.

 

Regards,

Jimmy Tao

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Direct Query and Time Intelligence functions

Hey Tamir,

 

basically the build-in Time Intelligence functions are syntax sugar for a combination of other DAX functions.

 

Here you will find a lot of examples how to rewrite these build-in functions:

https://www.daxpatterns.com/time-patterns/

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Direct Query and Time Intelligence functions

Hi there,

 

I was facing the same problem and I came up with the following code as a work around.  These scripts calculate measures such as month over month (MoM) and year over year (YoY).

 

Last Years Amount

Amount_LY = 
    CALCULATE(
        [AMOUNT],EXPENSES[DATE_YEAR] = VALUES(EXPENSES[DATE_YEAR])-1)

Last Months Amount

Amount_LM = 
    IF( (VALUES(EXPENSES[DATE_MONTH])-1) =0,
        CALCULATE(
            [AMOUNT],
            EXPENSES[DATE_YEAR] = (VALUES(EXPENSES[DATE_YEAR])-1),
            EXPENSES[DATE_MONTH] = 12
        ),
        CALCULATE(
            [AMOUNT],
            EXPENSES[DATE_YEAR] = (VALUES(EXPENSES[DATE_YEAR])-0),
            EXPENSES[DATE_MONTH] = (VALUES(EXPENSES[DATE_MONTH])-1)
        )
    )

You can use these basic calculations to derive measures like growth rates and differences.  As you can see in the 'Last Month Amount' code, you will have to get creative at times to replicate the time intelligence based calculations.

 

I hope this helps,

Eric

 


@Tamir wrote:

Hi,

 

Power BI has very powerful Time Intelligence capabilities.  

 

However, they seem not to be applicable when using a direct query.

 

Am I missing something big?

 

What is the alternative to the Time Intelligence functions when using Direct Query?

 

Thank you

Tamir


 

Highlighted
Frequent Visitor

Re: Direct Query and Time Intelligence functions

From msft docs:  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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors