Slow performance DAX

[VandanaPulluri]

Hi,

The following measure is taking to long to run 65238 millseconds. how can i re write as a VAR format or Please suggest any other way to imrove the performance.

DownTrend =
CALCULATE (
IF (
ISBLANK ( COUNTA ( 'DIm_Product'[Product_ID]) ),
BLANK (),
COUNTA ( 'DIm_Product'[Product_ID)
),
FILTER (
ALLSELECTED ('Dim_Sales'[Year_IND] ),
CALCULATE('_Measures'[p-value]<=0.05 && 'Dim_Sales'[TAU 1] < 0)
)
)
+0

Thank you,

[v-eachen-msft · Accepted solution]

Hi @VandanaPulluri ,

You could try the following DAX:

DownTrend =
VAR a =
    COUNTA ( 'DIm_Product'[Product_ID] )
VAR b =
    FILTER (
        ALLSELECTED ( 'Dim_Sales'[Year_IND] ),
        CALCULATE ( '_Measures'[p-value] <= 0.05 && 'Dim_Sales'[TAU 1] < 0 )
    )
RETURN
    CALCULATE ( SWITCH ( TRUE (), ISBLANK ( a ), BLANK (), a ), b ) + 0