cancel
Showing results for
Did you mean:
Frequent Visitor

## Compare a number with a range of numbers(from to) that exist in another table with no relation

Hi everyone!

I have stumpled upon a problem and I cannot think of any good solution!

My plan was to compare IP address of datacenters (range) with an IP that I have. If the IP that I have is within that range then to return me the datacenter name.

Comparing IP is not possible so I converted all IP into a decimal number! The table I now have is as follows :

My list to compare with

And I also have my IP's tables (this is a test table with random IP's) as follows :

Ip List

I converted the IP to decimal like I did in my datacenter list, now ideally I would like another column "Hosted By" which will be populated based on the datacenters list if the IP is within the range of the "IP From" and "IP to".

If its not within range just return "not a datacenter"

Is this even possible? As you see there are no relations in this table...

Any other ideas to make this work?

At the end of the day I want to see all the IP's that I have if they are on datacenters or unkown.... and I have ALOT of IP's to much...

I thought of creating a list with all the datacenter individual IP's (not as a range)... but the total is 95m different IP's, so the list will be huge and probably too cumbersome to use...

Looking forward to a smart solution to what I thought would be an easy problem....

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

## Re: Compare a number with a range of numbers(from to) that exist in another table with no relation

Hi

Thanks for your idea but I to tell you the truth I did not even try it... My table (IP) is 500K... I believe that would have it would have been quite slow...

I have solved this using the below DAX formula and it also quite fast! It take less than a minute to populate around 500K IP's that I have to check.

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] ))
2 REPLIES 2
Super Contributor

## Re: Compare a number with a range of numbers(from to) that exist in another table with no relation

not sure about the performance on this one, but it should give the correct result

the idea is to filter the original table for each row and then return the first entry

you can do it with custom function (create blank query in M and paste the code below in the Advanced Editor), name it fnFilterIPs

```(prmTable as table, prmDecimalIP as number) => let
Source = prmTable,
#"Filtered Rows" = Table.SelectRows(Source, each [IP from into decimal] <= prmDecimalIP and [IP to into decimal]>= prmDecimalIP),
Datacenter = try #"Filtered Rows"{0}[Datacenter] otherwise "Unknown"
in
Datacenter```

then you need to add new column to your Table2

`fnFilterIPs(Table1, [IP to into decimal])`

where Table1 is your first table

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Compare a number with a range of numbers(from to) that exist in another table with no relation

Hi

Thanks for your idea but I to tell you the truth I did not even try it... My table (IP) is 500K... I believe that would have it would have been quite slow...

I have solved this using the below DAX formula and it also quite fast! It take less than a minute to populate around 500K IP's that I have to check.

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] ))

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)