Turning nested SUMX into SUMMARIZE

[PshemekFLK]

Following this article: Nested SUMX or DAX Query? - P3 Adaptive I'm trying to turn nested SUMX formula into CALCULATE (SUMMARIZE) to improve performance

Nested SUMX (works correctly but extremely slow):

=SUMX( VALUES(dim_customer[id]),

                SUMX(VALUES(dim_product[id])),

                           [Plan])

SUMMARIZE:

=CALCULATE([Plan],

                      SUMMARIZE(fct_plan,dim_customer[id],dim_product[id])))

The problem is that the second formula gives me error:

The column 'dim_product[id]' specified in the 'SUMMARIZE' function was not found in the input table

I assume this is becuase fct_plan is connected to dim_product using bridge table on higher level than product[id] which is indeed not found in the fct_plan table:

Any way to adjust the SUMMARIZE formula to still be able to group by product[id]?

[v-chenwuz-msft]

Hi @PshemekFLK ,

You should make sure the 'fct_plan" be the many side of these tables. So that it can get the value it needs.

Or you can try something like this:

=CALCULATE([Plan],

                      FILTER(fct_plan,fct_plan[customerID] in values(dim_customer[id])),

                      FILTER(fct_plan,fct_plan[productID] in values(dim_product[id]))))

If you need more help, please share your pbix file without sensitive data and expect output.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @PshemekFLK 

There is no relationship whatsoever between the customer id and the product id. SUMMARIZE won't help here. The only table that can relate both is the CROSSJOIN which basically would be the same as nested SUMX. Consider also that SUMX is a very well optimized function.