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.
Hi I have a PowerBI report page that want some optimization.
Below query tabkes about 600ms. According to Marco Russo ideal time for a query is between 20-60ms.
In the below query it generates 23 storage engine queris. Formula Engine taks 418ms(75%) and Storage Engine Takes 146ms(25%).
There are few measures in the query which I belive effect the performance. The measures are as below. I have only put include the definition of one measure.
Re_Registraion_Overdue = MEASURE 'AHS RehousingList'[Re-Registraion Overdue] = IF (
ISBLANK (
CALCULATE (
COUNT ( 'AHS RehousingList'[Rehousing List Ref] ),
'AHS RehousingList'[Days to Re-Register] <= 0
)
),
0,
CALCULATE (
COUNT ( 'AHS RehousingList'[Rehousing List Ref] ),
'AHS RehousingList'[Days to Re-Register] <= 0
)
Rest of the mesures in the query are inside the evaluate part of the script and starts with Re_Registration_Due****
Here is the copy of the query.
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('AHS RehousingList'[Rehousing List Status Code])),
NOT(
OR(
'AHS RehousingList'[Rehousing List Status Code] = "HOU",
'AHS RehousingList'[Rehousing List Status Code] = "WD"
)
)
)
VAR __DS0FilterTable2 =
TREATAS({"Yes"}, 'AHS Community (Property)'[Managed])
EVALUATE
TOPN(
502,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('AHS Community (Property)'[Community], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
"Re_Registraion_Overdue", 'AHS RehousingList'[Re-Registraion Overdue],
"Re_Registraion_Due_8___14_Days", 'AHS RehousingList'[Re-Registraion Due 8 - 14 Days],
"Re_Registraion_Due_15___28_Days", 'AHS RehousingList'[Re-Registraion Due 15 - 28 Days],
"Re_Registraion_Due_0___7_Days", 'AHS RehousingList'[Re-Registraion Due 0 - 7 Days],
"Re_Registraion_Due__365_Days", 'AHS RehousingList'[Re-Registraion Due +365 Days],
"Re_Registraion_Due__29_Days", 'AHS RehousingList'[Re-Registraion Due +29 Days]
),
[IsGrandTotalRowTotal],
0,
'AHS Community (Property)'[Community],
1
)
ORDER BY
[IsGrandTotalRowTotal] DESC, 'AHS Community (Property)'[Community]
would anyone have how to optimise this query down to somewherebetween 20-50 ms
Thanks.
Solved! Go to Solution.
Hey,
without sample data, it is difficult to optimize the query performance, but at a first glance I would rewrite this:
[Re-Registraion Overdue] = IF ( ISBLANK ( CALCULATE ( COUNT ( 'AHS RehousingList'[Rehousing List Ref] ), 'AHS RehousingList'[Days to Re-Register] <= 0 ) ), 0, CALCULATE ( COUNT ( 'AHS RehousingList'[Rehousing List Ref] ), 'AHS RehousingList'[Days to Re-Register] <= 0 )
to this
Re_Registraion_Overdue = var avariable = CALCULATE ( COUNT ( 'AHS RehousingList'[Rehousing List Ref] ), 'AHS RehousingList'[Days to Re-Register] <= 0 ) return IF ( ISBLANK (avariable) ,0 ,avariable )
This should avoid a double evaluation of the CALCULATE() part.
Regards,
Tom
Hey,
without sample data, it is difficult to optimize the query performance, but at a first glance I would rewrite this:
[Re-Registraion Overdue] = IF ( ISBLANK ( CALCULATE ( COUNT ( 'AHS RehousingList'[Rehousing List Ref] ), 'AHS RehousingList'[Days to Re-Register] <= 0 ) ), 0, CALCULATE ( COUNT ( 'AHS RehousingList'[Rehousing List Ref] ), 'AHS RehousingList'[Days to Re-Register] <= 0 )
to this
Re_Registraion_Overdue = var avariable = CALCULATE ( COUNT ( 'AHS RehousingList'[Rehousing List Ref] ), 'AHS RehousingList'[Days to Re-Register] <= 0 ) return IF ( ISBLANK (avariable) ,0 ,avariable )
This should avoid a double evaluation of the CALCULATE() part.
Regards,
Tom
Thanks a lot Tom,
Yes using a variable resuces the time of the calculation as it only calculate ones and only if needed.
It helped too.
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |