DAX Optimizer - Filtered table as iterator

Question

Hi,&nbsp;&nbsp;I just tried out the new free trial version of DAX optimizer tool and it flagged the issue with one of my measures. I was suspecting that it may be the one cuasing the performance issues in one of my reports so it's nice that the tool confirmed it. It flags the issue as "Filtered table as iterator" so I understand that putting a filtered table in the sumx is causing problems. Any idea how I could change the measure below to be less heavy on performance?&nbsp;&nbsp;Final POS Total Calculated Cost Trx =SUMX (&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; //sums all the distributors that doesn't have detailed POS and ensuring that detailed level will result in zero&nbsp; &nbsp; &nbsp; &nbsp; fct_high_level_pos_inv,&nbsp; &nbsp; &nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HASONEFILTER ( dim_product[Product Family Code] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || HASONEFILTER ( dim_product[BU (+)] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || HASONEFILTER ( dim_product[Model Group Code] ) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || HASONEFILTER ( dim_product[Market Model Code] ) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || HASONEFILTER ( dim_product[Model] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || HASONEFILTER ( dim_product[Item No (f)] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK (),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fct_detailed_pos,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND(fct_detailed_pos[Provider No] = fct_high_level_pos_inv[Provider No],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fct_detailed_pos[POS Month (+)] = fct_high_level_pos_inv[POS Month (+)])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) = 0&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; fct_high_level_pos_inv[HL POS Cost Trx]*RELATED(dim_distributor[Net Revenue Factor POS (+)]))+ [Det POS_Total Calculated Cost Trx]&nbsp;&nbsp;// then adds all the detailed POS&nbsp;&nbsp;What the measure doing is summing all the high level POS (from&nbsp;fct_high_level_pos_inv table) for those distributors that didn't report detailed POS (in&nbsp;fct_detailed_pos table) and ensuring that when filtering on detailed level (HASONEFILTER (dim_product[Product Family Code] )&nbsp;|| HASONEFILTER ( dim_product[BU (+)] ) etc.) the measure will result in zero instead of showing a detailed number.&nbsp;

lbendlin · Answer

Their documentation/knowledge base shows potential workarounds. As always you want to use DAX Studio to validate that the change results in an actual improvement.

Forum Discussion

DAX Optimizer - Filtered table as iterator

1 Reply

Related content

Optimize double iteration measure in DAX

Optimize Measures

Fabric Cost optimization

DAX performance optimization

Dax code optimization