Check IPs with a range of IP's (from datacenters list)

[aabi]

Hello Everyone

I have a problem and I'm not sure what is the best way to solve it.... and also correctly...

I have a list with all datacenters in the world (found at https://github.com/client9/ipcat/blob/master/datacenters.csv if anyone want this).

example of the list/table is as follows:

Ip From	Ip To	Datacenter Provider
13.64.0.0	13.71.255.255	Microsoft Azure
13.72.64.0	13.72.127.255	Microsoft Azure

And I also have many IP's that I want to compare with that list/table so that I can figure from which datacenter this IP comes from.

For example an IP 13.64.12.12 will be within the range of the list (as its within the 13.64.0.0 to 13.71.255.255) so it will be Microsoft Azure

While the IP 13.72.63.0 its not in the list (as its below the value 13.72.64.0) thus it will be "Not Datacenter"

I would like to note that I found saw this solution https://community.powerbi.com/t5/Desktop/Turn-IP-Range-into-list-of-IP-s/m-p/538212 however in my case is not applicable as is not only the last "." and also if you see the list that I have it will create millions of entries!

It will need very heavy processing!

[v-frfei-msft · Accepted solution]

Hi @aabi ,

Please check the following steps as below.

1. Split the ip columns by "." in power query.

2. To create calculated columns as below.

ip = iplist[ip.1]*100000000+iplist[ip.2]*1000000+iplist[ip.3]*1000+iplist[ip.4]

ipfrom = 'Table'[Ip From.1]*100000000+'Table'[Ip From.2]*1000000+'Table'[Ip From.3]*1000+'Table'[Ip From.4]

ipto = 'Table'[Ip To.1]*100000000+'Table'[Ip To.2]*1000000+'Table'[Ip To.3]*1000+'Table'[Ip To.4]

3. After that, we can achieve our goal by a calculated column in ip table.

isornot = 
VAR cr =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[ipfrom] <= iplist[ip] && 'Table'[ipto] >= iplist[ip] )
    )
RETURN
    IF ( ISBLANK ( cr ), "Not Datacenter", "Datacenter" )

Pbix as attached.

[aabi]

Hi Frank

Thank you for this! You helped alot!

While this works as you made it, and I can differiantiate between "Datacenters" and "Not Datacenters".

It's not what I need, because the results that I want is the actual datacenter name. Just my example might not have been that good as I only had Azure as datacenter.

I really appreciated the idea/logic of using COUNTROWS, to find the range as my main issue when I was trying to make a filter was that I could not select the table (by using normal count) and I got stuck!

I changed the formula as follows and now I'm getting the result that I want!

Hosted by =

CALCULATE (

ALLSELECTED(datacenters[Datacenter]),

FILTER ( 'datacenters', 'datacenters'[IP from into Decimal] <= 'Ip''s'[IP into Decimal] && datacenters[IP to into Decimal] >= 'Ip''s'[IP into Decimal] ))

Keep it simple!:)

P.S. Also the idea for using the splitextbydelimiter was very good! It never crossed my mind of doing that and instead I spent time to make a formula that reads the "." with MID and FIND to make the breakdown of the IP!