DAX Optimization

[amaleranda]

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.

[TomMartens · Accepted 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

[amaleranda]

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.