A Performance Problem

[AlejandroPCar]

Hi

I have a slow problem here. I have the follow measure: 

classifier = 
    VAR initial = MIN(conditions_table[initial_age])
    VAR final = MIN(conditions_table[final_age])
    VAR current = MIN(people_table[current_age)
RETURN
    IF(
        SUM(people_table[people]) = BLANK(),
        BLANK(),
        IF(
            AND(
                current >= inicial,
                current <= final
                ), 
            1, 
            0
        )
    )

Then the previous measure is in

programs = 
    VAR sum = SUMX(
        people_table,
        SUMX(
        conditions_table,
        classifier
        )
    )
RETURN
    sum

 My problem is the performance is absolutly slow. I tried before with CROSSJOIN instead the double SUMX but was worst. The idea is simple: There are over half million people in the people table. Each of them has the chance of be in the age range of every program in the conditions_table. So a person could belong to several programs at time. Currently there are over 300 conditions in the conditions table. My model is now compound by this two tables and other ones only for the purpose to serve as filters. 

So the target is for example, person A is in the program_group 1 and he can be in 13 programs but his age only allows him to be in 9. person B is the same group so can be in the same 13 programs but she is restricted to be in only 7. Both persons are from country A, so the total programs in country A will be 9 + 13 = 22. 

I use the classifier measure beacuse the result 0 or 1 is perfect to summarize and get totals. My two main tables are related by a both side many to many relationship (program_group column in both tables, this column has the program_group ID, and each ID have multiple programs that have its own age conditions). 

Hope someone can help me to improve my model performance. 

Thanks a lot. 

[v-yuezhe-msft · Accepted solution]

@AlejandroPCar,

Nested SUMX executes slow because SUMX is an iterator and hence it will step through every row in the table 1 row at a time, when SUMX are nested, there is a multiplicative effect.

In your scenario, please use SUMMARIZE with ADDCOLUMNS and FILTER as described in this similar blog: https://powerpivotpro.com/2015/08/nested-sumx-or-dax-query/.

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@AlejandroPCar,

Nested SUMX executes slow because SUMX is an iterator and hence it will step through every row in the table 1 row at a time, when SUMX are nested, there is a multiplicative effect.

In your scenario, please use SUMMARIZE with ADDCOLUMNS and FILTER as described in this similar blog: https://powerpivotpro.com/2015/08/nested-sumx-or-dax-query/.

Regards,
Lydia

[AlejandroPCar · Accepted solution]

Hi @v-yuezhe-msft

Thanks for your response and after several, several, several attempts finally I achieve a greatly boost of 50% faster measures. Thanks for your advice.