DAX Measure Very Slow

Question

Hello,&nbsp;I have a Measure where I am calculating the number of occurences (repetitions) of a Key within 'N' Days (Based on Parameter Selection from Slicer) before &amp; after the date mentioned in each row. Also, the occurence needs to be counted only if the Net Price is either lower by 10% or more, or higher than 10% or more.&nbsp;I have the following meaasure, but it takes 6-8 minutes for computing every time, but I am looking for a quicker result. Please guide.&nbsp;Thanks in Advance..!&nbsp;DAX&nbsp;Count =&nbsp;VAR DT1 = MIN(Data[Created On]) - Parameter[Parameter Value]VAR DT2 = MIN(Data[Created On]) + Parameter[Parameter Value]&nbsp;RETURN&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp; COUNT(Data[Key]),&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL(Data),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data[Created On] &gt;= DT1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data[Created On] &lt;= DT2,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data[Key] = SELECTEDVALUE(Data[Key])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OR(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data[Net Price] &lt;= (SELECTEDVALUE(Data[Net Price]) * 0.90),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data[Net Price] &gt;= (SELECTEDVALUE(Data[Net Price]) * 1.10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; ))

greg_deckler · Answer

Anonymous&nbsp;Would need to understand your data better. I wrote some blog articles on performance. One of the keys would be to eliminate as many rows as possible as early as possible within your calculation.&nbsp;
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813
&nbsp;

fowmy · Answer

Anonymous&nbsp;Not sure about your data model the table column and row count, anyway, try your measure this way:Count =
VAR DT1 =   MIN ( Data[Created On] ) - Parameter[Parameter Value]
VAR DT2 =   MIN ( Data[Created On] ) + Parameter[Parameter Value]
VAR DTKY = SELECTEDVALUE ( Data[Key]
VAR MINNETPRICE = SELECTEDVALUE ( Data[Net Price] ) * 0.90
VAR MAXNETPRICE = SELECTEDVALUE ( Data[Net Price] ) * 1.10

RETURN
CALCULATE (
    COUNT ( Data[Key] ),
    FILTER (
        ALL ( Data[Created On] , Data[Key] , Data[Net Price] ),
        Data[Created On] &gt;= DT1 &amp;&amp; Data[Created On] &lt;= DT2 &amp;&amp; Data[Key] =  DTKY  &amp;&amp; (Data[Net Price] &lt;= MINNETPRICE || Data[Net Price] &gt;= MAXNETPRICE)
    )
)________________________If my answer was helpful, please consider Accept it as the solution to help the other members find itClick on the Thumbs-Up icon if you like this reply 🙂YouTube&nbsp; LinkedIn

Forum Discussion

DAX Measure Very Slow

2 Replies

Related content

Surprisingly slow measure

Slow Measures

Measure slows down report

Snowflake connexion too slow

Report very slow