List location based on IP address from a range in another table

[Data4920]

I'm trying to match up a location to a specific asset based on the assets IP address given a list of ranges from another table.  Neither table has a unique column that will allow me to join them together.

The ASSET table lists a single IP address per asset however it may be listed many times based on the number of attributes it might have. 

ASSET Table

IP

<IP1>

<IP2>

<IP2>

<IP2>

<IP3>

<IP3>

<IP3>

<IP3>

 The NETWORK table lists all of the starting and ending IP's per range for all existing subnets at that location.

NETWORK Table
Location	Network ID	Beginning IP	Ending IP
Site 1	N_ID1
Site 1	N_ID2
Site 1	N_ID3
Site 2	N_ID4
Site 2	N_ID5

What I've done so far (which I'm happy to change if it results in a working query) is to convert both the beginning and ending IP's in the NETWORK table and the IP in the ASSET table to a single number by splitting them up by '.' delimiters then adding them back together in Power Query by using this ("IPStart", each [IP Start.1]*1000000000 + [IP Start.2]*1000000 + [IP Start.3]*1000 + [IP Start.4], type number)).  To add the location to the Asset table, I was hoping to do something like IF ASSET[IP] is >= IPStart AND ASSET[IP] <= IPEnd then list location but I’m not sure how to make the connection between tables and list the correct location.  Any help would be greatly appreciated!

[lbendlin]

Do you have last digit subnet granularity or is it sufficient to look at the first three digits? IE do your list ranges ever specify anything besides 0 and 255 in the fourth digit?

Are the site address ranges overlapping? ie can an asset potentially be mapped to multiple ranges, or are all your ranges mutually exclusive?

What should happen if the IP doesn't fall into any of the listed subnets?

The calculated column would be relatively simple - for each IP/Asset you would check if any subnet start is smaller than the IP or equal, and then from that result list you check which subnet end is higher or equal than the IP.

[Data4920]

The entire range would need to be evaluated since all 4 digits could vary even within a single site given the different networks, especially in some larger data centers.  I've tried to provide another example below from the Network table with some dummy IP's including the converted IP number that I created to attemp the lookup.  Each range is identified by the Network ID and is continuous from IPStart to IPEnd.  The IP's that I need to lookup from the Asset table are in the same format as IPStartNum and once PBI has identified that it falls within one of the Network ID ranges, I need the location to be displayed in the Asset in a new column.  Now there are some assets with a common 192.168.X.X IP's that you might see on a home or guest network but those would not be mapped to a location since they are not officially company ranges so I'd probably list the location as Unknown along with anything else not found.  Aside from that the ranges are mutually exclusive.

The calculated column would be relatively simply but without the tables being joined in anyway, I'm not sure how to do write it so that the Asset IP scans every row in the Network table until it finds a match.  TIA!

Location	Network ID	IPStart	IPEnd	IPStartNum	IPEndNum
Site A	Site A_1	1.20.50.1	1.20.50.254	1020050001	1020050254
Site A	Site A_2	2.35.40.1	2.35.43.254	2035040001	2035043254
Site A	Site A_3	2.40.40.6	2.40.40.7	2040040006	2040040007

[lbendlin · Accepted solution]

Not sure if I am oversimplifying this but here's a version. Calculated Column in the IPs table.  No relationship between tables.

NetworkID = 
var n = IPs[IPNum]
return CALCULATE(max(Networks[Network ID]),Networks[IPStartNum]<=n,Networks[IPEndNum]>=n)

Here are the definitions for the queries I used:

Networks:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYy7CoAwDEV/RTqXcJs07ew3OEpxcnD3/zF9gAURMpxDuGff3Xbd57I6P+AIhoEYpKCZWWM1MKAAwiv1U/y3xIZMohR7qbOMEkMUcZS6yF9J2toydmni3KYWqZ00S3alPA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Network ID" = _t, IPStart = _t, IPEnd = _t, IPStartNum = _t, IPEndNum = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IPStartNum", Int64.Type}, {"IPEndNum", Int64.Type}})
in
    #"Changed Type"

IPs:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcnBCcAwDEPRXXwOQlbi0swSsv8alQtfB/HOidxCPi8msmJEavuRk7532CGiXCNFFr2fhMWuSVzsmu4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IP = _t, IPNum = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IPNum", Int64.Type}})
in
    #"Changed Type"

[gabriel007]

Thanks worked for me

[Data4920]

Using the DAX provided produced an error "Cannot convert value 'Site A_1' of type Text to type Integer", where Site A_1 is a valid Network ID.  All of the Network ID's are alphanumeric (i.e. ABCD1, BCDE3) and listed in the column as type text.  Does that change the 'Calculate(Max' approach or is there away to do some kind of conversion during the query?  I feel like that would work if the network ID was strictly numeric.

[lbendlin]

Try my solution in a blank Power BI Desktop file.  The DAX error must be elsewhere.