Forum Discussion
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
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-directquery-mode-ssas-2016?view=sql-server-2017. 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
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
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.