Forum Discussion
List location based on IP address from a range in another table
- 6 years ago
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"
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"
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.
- lbendlin6 years ago
Super User
Try my solution in a blank Power BI Desktop file. The DAX error must be elsewhere.