Hi,
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?
Final POS Total Calculated Cost Trx =
SUMX (
FILTER (
//sums all the distributors that doesn't have detailed POS and ensuring that detailed level will result in zero
fct_high_level_pos_inv,
IF (
HASONEFILTER ( dim_product[Product Family Code] )
|| HASONEFILTER ( dim_product[BU (+)] )
|| HASONEFILTER ( dim_product[Model Group Code] )
|| HASONEFILTER ( dim_product[Market Model Code] )
|| HASONEFILTER ( dim_product[Model] )
|| HASONEFILTER ( dim_product[Item No (f)] ),
BLANK (),
COUNTROWS (
FILTER (
fct_detailed_pos,
AND(fct_detailed_pos[Provider No] = fct_high_level_pos_inv[Provider No],
fct_detailed_pos[POS Month (+)] = fct_high_level_pos_inv[POS Month (+)])
)
) = 0
)
),
fct_high_level_pos_inv[HL POS Cost Trx]*RELATED(dim_distributor[Net Revenue Factor POS (+)])
)+ [Det POS_Total Calculated Cost Trx] // then adds all the detailed POS
What the measure doing is summing all the high level POS (from fct_high_level_pos_inv table) for those distributors that didn't report detailed POS (in fct_detailed_pos table) and ensuring that when filtering on detailed level (HASONEFILTER (dim_product[Product Family Code] ) || HASONEFILTER ( dim_product[BU (+)] ) etc.) the measure will result in zero instead of showing a detailed number.
