Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
never mind 🙂 i found a great solution:
http://academy.tumbleroad.com/courses/how-to-parse-multi-value-columns-in-power-bi
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
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!
never mind 🙂 i found a great solution:
http://academy.tumbleroad.com/courses/how-to-parse-multi-value-columns-in-power-bi
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |