Forum Discussion

rks's avatar
rks
Resolver II
5 years ago
Solved

Very Slow Nested Iterator

Hi Community.   I have the following scenario: * I have a product table containing 15 million products * I have a stock table (a billion or so rows) with a relationship to the product * The stoc...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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...

AlB's avatar
AlB
Community Champion
5 years ago

Hi rks 

You talk about two tables but your code refers to three: 'Filter Product', 'Filter Parts'  and 'Stock'.  Can you explain a bit more and show the relationships between the tables (what fields are involved)?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers