Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |