Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to create Quarter-on-Quarter % Change in Direct Query mode in Power BI?

 

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?

Need Help

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    HI, Anonymous

    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

     

    and for your  Quarter-on-Quarter % measure, you could try this way

    Step1:

    Add a date table with this columns  [year], [quarter], [year quarter] and a rank column for [year quarter] in the data source

    Step2:

    After get data then create a relationship between services_user stat_view with date table

    Step3:

    Use this formula to create a Quarter-on-Quarter % measure

    Measure = 
IF(
	ISFILTERED('Date'[YQRANK]),
	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(SUM('services_user stat_view'[Qty]), FILTER(ALL('Date'),MAX('Date'[YQRANK])='Date'[YQRANK]+1))
	RETURN
		DIVIDE(SUM('services_user stat_view'[Qty]) - __PREV_QUARTER, __PREV_QUARTER)
)

    Note:

    Why do we need to add a column and a rank column for [year quarter] ?

    for example:

    This is just to make it easy to calculate Quarter-on-Quarter % Change for year 2018 Q4 and year 2019 Q1

     

    and here is pbix, you could refer to it.but all the date column is done in the data source.

     

    Best Regards,

    Lin