cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aabi Frequent Visitor
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 :

Datacenters.JPGMy list to compare with

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

IP Lists.JPGIp 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
aabi Frequent Visitor
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] ))

View solution in original post

2 REPLIES 2
Stachu Super Contributor
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!

aabi Frequent Visitor
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] ))

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

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

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)