sumx consuming function?

[MP_123]

is anyone encountered with problem with sumx function?

i have a report with millions of rows and the power bi sometimes stuck and i think that because of this dax query...

is someone encountered this?

thanks

[v-caliao-msft]

Hi @MP_123,

SUMX is an itertor. Unlike SUM which can operate on blocks of data and very efficient , SUMX steps through your data one row at a time  and is less efficient. Therefore look to use SUMX only when you cannot  use SUM.  Besides, please check if you can use calculated instead or not.

If this is not what you want, pelase provide us some details about your data, so that we can make further analysis.

Reference
https://www.powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/

Regards,

Charlie Liao

[MP_123]

hi! @v-caliao-msft thank you!

my data is a unique users table, and it looks like that:

date           Country          deviceType        UserCount

1/1/11       US;IN;AUS        PC;Tablet           25

1/2/11        GB;US              Phone                5

1/3/11         RU;AUS           PC;Phone          3

this means that there are 25 users that are connected to USA,India and Australia and have PC and Tablet.

my report should work like this: if i select PC on slicers i want to get 28 (users that have also PC). If i select US i want to get 30.

If i select PC and Phone, i will get 33 (users that have Pc or phone)

my slicers shows only the single values: US, IN, AUS, GB, RU. and PC, Tablet, Phone (different tables)

to overcome the concatenation issue, i created a sql query on power bi and now i'm imported the table to look like:

date           Country          deviceType        SingleCountry              singleDevice    UserCount
1/1/11       US;IN;AUS        PC;Tablet             US                                         PC                  25

1/1/11       US;IN;AUS        PC;Tablet             US                                       Tablet              25

1/1/11       US;IN;AUS        PC;Tablet             IN                                         PC                  25
1/1/11       US;IN;AUS        PC;Tablet             IN                                       Tablet              25

1/1/11       US;IN;AUS        PC;Tablet            AUS                                       PC                  25
1/1/11       US;IN;AUS        PC;Tablet            AUS                                     Tablet              25
1/2/11        GB;US              Phone                  GB                                        Phone             5

1/2/11        GB;US              Phone                  US                                        Phone             5
1/3/11         RU;AUS           PC;Phone            RU                                         PC                  3

1/3/11         RU;AUS           PC;Phone            RU                                       Phone              3

1/3/11         RU;AUS           PC;Phone           AUS                                      PC                    3

1/3/11         RU;AUS           PC;Phone           AUS                                      Phone              3

now, if i select PC for example, i will get 25+25+25+3+3 and it's not correct.

for this reason, i added a calculated measure to count the rows that are the same except the singles values.

date           Country          deviceType        SingleCountry              singleDevice    UserCount    #Rows
1/1/11       US;IN;AUS        PC;Tablet             US                                         PC                  25         6
1/1/11       US;IN;AUS        PC;Tablet             US                                       Tablet              25          6
1/1/11       US;IN;AUS        PC;Tablet             IN                                         PC                  25          6
1/1/11       US;IN;AUS        PC;Tablet             IN                                       Tablet              25          6
1/1/11       US;IN;AUS        PC;Tablet            AUS                                       PC                  25          6
1/1/11       US;IN;AUS        PC;Tablet            AUS                                     Tablet              25          6
1/2/11        GB;US              Phone                  GB                                        Phone             5           2
1/2/11        GB;US              Phone                  US                                        Phone             5          2
1/3/11         RU;AUS           PC;Phone            RU                                         PC                  3           4
1/3/11         RU;AUS           PC;Phone            RU                                       Phone              3          4
1/3/11         RU;AUS           PC;Phone           AUS                                      PC                    3         4
1/3/11         RU;AUS           PC;Phone           AUS                                      Phone              3           4

now, if i want to know how many user i have, i sumx the dividison between the UserCount and the # Rows.

for example, the total Users will be:

25/6+25/6+25/6+25/6+25/6+25/6+ 5/2+5/2+3/4+3/4+3/4+3/4 = 33

If PC is selected: the table will look like:

date           Country          deviceType        SingleCountry              singleDevice    UserCount    #Rows
1/1/11       US;IN;AUS        PC;Tablet             US                                         PC                  25        3
1/1/11       US;IN;AUS        PC;Tablet             IN                                         PC                  25         3
1/1/11       US;IN;AUS        PC;Tablet            AUS                                       PC                  25          3
1/3/11         RU;AUS           PC;Phone            RU                                         PC                  3           2
1/3/11         RU;AUS           PC;Phone           AUS                                      PC                    3        2

and the Sumx will be 25/3+25/3+25/3+3/2+3/2

if i will use sum here, it will be (25+25+25+3+3)/(3+3+3+2+2) and i will get the wrong answer.

important to say - my inital table, before splitting the values is about 1 million row, so after the splitting you understand how many rows it's becoming...

hope you understand, please ask me if you have questions

thanks!

[MP_123 · Accepted solution]

never mind 🙂 i found a great solution:

http://academy.tumbleroad.com/courses/how-to-parse-multi-value-columns-in-power-bi

[Dan80]

Sumx is a performance killer, especially in your case when you have so much data because every time you click a slicer the sumx recaculates and that will take a while for millions of rows! Alternative is to use a calculated column or do the calculation in power query.