Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a list of values, in this case internal IP addresses, that I am trying to relate to a physicall location. The list of IPs I have are all exact IP addresses and I am trying to get the general location.
IP Addresses:
192.13.45.25
192.13.42.08
192.13.19.26
168.192.4.35
168.192.24.13
What I was attempting was to use the SWITCH to change 192.13.* addresses to NYC, while 168.192.* to Boston, ect. for ~500 subnets. However I couldn't see to get any wildcards to work within the IP address, is there anyway to do that? This is what I had:
Solved! Go to Solution.
1. In DAX you can do it like this:
Column =
SWITCH(
LEFT('Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:])+1)),
"192.13.", "NYC",
"168.192.", "Boston"
)
2. With Power Query you get the following solution:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ00jM01jMx1TMyVYrVQQgY6RlYIAsYWuoZmUEEzCz0QIImesamKAJGJkCFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IP Addresses:" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.BeforeDelimiter([#"IP Addresses:"], ".", 1) = "192.13" then "NYC" else "Boston", type text)
in
#"Added Custom"
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Nicely done!!
Hello, @keithkeirstead, you may want to test this type of measurement or expand the location options with more subnet masks in line with your actual dataset,
Location =
SWITCH (
FALSE (),
ISERROR ( SEARCH ( "192.13.", MAX ( ReleaseIP[IP Address] ) ) ), "NYC",
ISERROR ( SEARCH ( "168.192.", MAX ( ReleaseIP[IP Address] ) ) ), "BOSTON",
"Other City"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
1. In DAX you can do it like this:
Column =
SWITCH(
LEFT('Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:])+1)),
"192.13.", "NYC",
"168.192.", "Boston"
)
2. With Power Query you get the following solution:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ00jM01jMx1TMyVYrVQQgY6RlYIAsYWuoZmUEEzCz0QIImesamKAJGJkCFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IP Addresses:" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.BeforeDelimiter([#"IP Addresses:"], ".", 1) = "192.13" then "NYC" else "Boston", type text)
in
#"Added Custom"
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)