How to create time intelligence measures in Direct Query mode in Power BI?

0
votes

I want to create Quarter-on-Quarter % Change in Direct Query mode in Power BI

Creating measures like Quarter on Quarter percentage is quite easy is Import mode in power BI. But if we try them in Direct Query mode, the time intelligence measures are not available.

I even tried the DAX generated for Quarter-on-Quarter % Change in Import mode(as shown below) to do the same in Direct Query mode. But Time intelligence functions does not work in Direct Query mode.

    Count of IssueId QoQ% = 
IF(
    ISFILTERED('services_user stat_view'[CreatedDate]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_QUARTER =
        CALCULATE(
            COUNTA('services_user stat_view'[IssueId]),
            DATEADD(
                'services_user stat_view'[CreatedDate].[Date],
                -1,
                QUARTER
            )
        )
    RETURN
        DIVIDE(
            COUNTA('services_user stat_view'[IssueId])
                - __PREV_QUARTER,
            __PREV_QUARTER
        )
)

Could you please help?

2
powerbidax
From Microsoft help: "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". docs.microsoft.com/en-us/power-bi/desktop-directquery-about – RADO

2 Answers

0
votes

Create a proper Date Table, and mark it as such. And use that date table on your Time Intelligence. Should work as expected.

0
votes

You are probably using FORMAT in your date configuration, which is not supported.

If you don't find it in these tables, then you cannot use.

Hope this helps!