Best way to filter multiple IP addresses

[Anonymous]

I have a large set of data that has a column containing computer IP addresses.  These addresses are across a large number of IP ranges.  I want to filter out a combination of ranges found within the large set of range. 

For example I have IP's in the range of 192.168.0.0-192.168.100.100

I want to filter out smaller subset of ranges and individual IP's from this large range e.g. filter out :

192.168.10.0-192.168.10.254

192.168.30.0-192.168.30.254

192.168.82.0-192.168.82.100

192.168.61.1

192.168.75.10

etc...

What the best way to do this?  Can I create a "group" add the ranges and the indivdal IPs and filter using the "group"?

PS im a total PowerBI noob so go easy please.  Thanks.

[V-pazhen-msft · Accepted solution]

@Anonymous 

Check attached pbix for detail.

You can split the IP column into 2 parts, the fix 192.169 and then end IP strings, and change it to decimal type to filter. 

If you want to create separate table, you can just use if function on the IP2 column to split the IP in different tables. For example: 

ip 10. = CALCULATETABLE(SUMMARIZE('Table',[IP]),FILTER('Table',[IP2]>=10.000 && [IP2]<=10.254))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Anonymous ,

welcome tom Power BI, hopefully you will enjoy your journey.

Now to your question, I would do the folling, create 4 separate numeric columns (whole integer) inside the table that stores the the IP4 adresses.

Then you can use these columns inside a numeric slicer and filter the ip's.

Maybe you will find this article interesting, in this article I describe how you can convert an ip4 adress into a number: https://www.minceddata.info/2018/07/27/using-power-query-to-conver-an-ip4-address-into-a-numeric-value/ Another approach for filtering IP adresses could be to create slicers that define ranges, these ranges then are converted to a number and this number is used to filter the ips. 

Hopefully, this provides some new ideas on how to tackle your challenge.

Regards,

Tom