Forum Discussion
Very Slow Nested Iterator
Your formula will be faster if you create an aggregation table that will squeeze the 'Stock' table so that you don't have to count rows for PartNo in CALCULATE ( COUNTROWS ('Stock') ) but use the number of rows directly. So, you'd have a table aggStock which would be created by appropriately agrouping rows and adding one more column that would tell you the number of rows in the grouping (aggStock[PartCount]). Then the measure would be:
sumx( 'Products', calculate( minx( 'aggStock'[PartCount] ) ) )In aggStock you'd have the same columns as in Stock but it would not be as granular, so that you don't have to count the rows. This is the first speed-up that comes to my mind...
Your formula will be faster if you create an aggregation table that will squeeze the 'Stock' table so that you don't have to count rows for PartNo in CALCULATE ( COUNTROWS ('Stock') ) but use the number of rows directly. So, you'd have a table aggStock which would be created by appropriately agrouping rows and adding one more column that would tell you the number of rows in the grouping (aggStock[PartCount]). Then the measure would be:
sumx(
'Products',
calculate( minx( 'aggStock'[PartCount] ) )
)
In aggStock you'd have the same columns as in Stock but it would not be as granular, so that you don't have to count the rows. This is the first speed-up that comes to my mind...
I accept the change in a data model as a solution. Obviously the nested iterator in itself is not to be optimized...
- Can you tell us what kind of performance boost you've got from the suggestion? How have the runtimes changed?
By the way, you don't have to use MINX. You can do:
sumx( 'Products', calculate( min( 'aggStock'[PartCount] ) ) )However, under the hood MIN is always MINX:
min( T[Col] ) = minx( T, T[Col] )
