Forum Discussion
Locating users based on IP takes too long
Hi,
So I have 2 tables - 1 with users and the other with IP ranges (3 columns (ipfrom, ipto, country).
The formula that I am trying to use is the following:
Country = CALCULATE(VALUES('table1_ranges'[countryName]);filter('table1_ranges';'table2'[ip]>('table1_ranges'[ipFrom]-1) && 'table2'[ip] <= 'table1_ranges'[ipTo]))
However, it takes too long to calculate and does not even finalize the calculations it just keeps "Working on it" for very long time and then I close it.
I've already minimized the number of tables, row and columns as much as possible.
Table 1 is with 370 000 rows and table 1 with 1 000 000.
Is there another solution for matchin IP with a range so that I can ge the country?
Thanks you
2 Replies
v-caliao-msftMicrosoft Employee
370 000 rows is not too large for Power BI. In your scenario, could you please provide us the detail table structure with some sample data and your expected result, so that we can make further analysis.
Regards,
Charlie Liao
Ok so I have 3 tables :
userId username ip ipnumber 1 a 127.255.555.666 2147483647 2 s 127.255.555.667 2147483648 3 d 127.255.555.668 2147483649 4 f 127.255.555.669 2147483650 5 g 127.255.555.670 2147483651 ipFrom ipTo Country 34605056 34605311 USA 3278942788 3278942789 Germany 2147483647 2147483653 Poland 2157483669 2157483670 UK 3113321472 3113322495 Austria Those two I use to get the country name for the respective idnumber.
The third table is used to get the users that actually played games during some period and get the game the played and the platform.
platform userid date game a 1 1.7.2017 asd b 1 1.7.2017 fgh c 2 3.7.2017 asd d 3 3.7.2017 asd e 3 3.7.2017 fgh The formula for getting the country name is inserted as a column in the first table.
