Dax Query optimization

Question

Hello,&nbsp;Below query is for Total Sum of the positive values in tablur report, working as expected but liitle slow.&nbsp;How to rewrite it in optimize way?&nbsp;Correct Total Sum = SUMX(FILTER(ADDCOLUMNS(SUMMARIZE(Fact,dimA[Aname],dimB[BName]),"@MeasureA",[MeasureA],"@MeasureB",[MeasureB)],"@MeasureC",[MeasureC]),[@MeasureA]&gt;0 &amp;&amp; [@MeasureB]&gt;0 &amp;&amp; [@MeasureC]&gt;0),[@MeasureA])&nbsp;Thanks,

amitchandak · Answer

joepath , this seems fine, need to check calculation for three measures

joepath · Answer

amitchandak&nbsp; Measure have basic avg calculations, Is there any way to replace filter function with calculatedtable? I see filter always create prob on a big table.

mahoneypat · Answer

I agree that measure looks pretty good, and would have written it the same way. How big is your Fact table and how many rows should that SUMMARIZE table have?&nbsp; One thing you could try would be to filter it sequentially with FILTER(FILTER(FILTER. Not sure it will help much but easy to try.
&nbsp;
Correct&nbsp;Total&nbsp;Sum&nbsp;=SUMX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE&nbsp;(&nbsp;Fact,&nbsp;dimA[Aname],&nbsp;dimB[BName]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"@MeasureA",&nbsp;[MeasureA]&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;[@MeasureA]&nbsp;&gt;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[MeasureB]&nbsp;&gt;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[MeasureC]&nbsp;&gt;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;[@MeasureA])
&nbsp;
Pat

bcdobbs · Answer

Is there any relationship between measure a, b and c. Just wondering if you could look at it from a maths point of view as to what needs to be true for all 3 to be greater than zero. That might be faster to test than actually evaluating the full values.

If you create a calculated table which materialises the table inside the sumx how many rows do you get? I agree with the others. Initial thought is it looks "ok". How slow is the query?

Only other thought is depending on what you need to slice by are you able to preagregate anything in a calculated table to move some evaluation away to model processing instead of live at query time.

joepath · Answer

Thanks all for looking into this, seems not much changes is required,

Forum Discussion

Dax Query optimization

7 Replies

Related content

DAX query with SUMMARIZECOLUMNS - Optimization

DAX performance optimization

Dax code optimization

DAX Query help

DAX query needs optimization