IP Address Compare and Categorize

[austinjgreer]

Hello,

I'm hoping to get some help with this. I've tried a few things but haven't been able to get exactly what I need. Apologies if there's an easy solution to this, I'm still learning how to use PowerBI.

Basically, I'm going to be running discovery scans every month and would like to automate the analysis as much as possible.

What I need to do is compare the IP Addresses in the current month's export with IP Addresses in the previous month's export. For IP Addresses that are in both, put "Old" in the row next to said IP under a new column. For IP Addresses that are only in the current month's export, put "New." 

The closest I've gotten to it so far was:

NewColumn = IF([PreviousMonthIPs] = [CurrentMonthIPs], "Old" , "New"

The problem with this is it's comparing the two IPs in the same row. And if they don't match (they won't, since there are new IPs in the second column) then it puts "New" in the third column. Is there a way to compare the two columns and find duplicates within the whole column? 

Another idea I had was to just combine the IPs from both exports into one column and do something like:

If this IP is a duplicate, output "Old" into a new column. If it's new, output "New" into that column. 

And just go down the column that way. But I don't want it to do it for each duplicate IP found, only the first one. 

Thanks in advance for any help! 

[freginier]

You can do that in M or DAX.

Do you share a dataset ?

[austinjgreer]

Unfortunately I can't share a screenshot but it's basically this:

Column 1: 2021-12_IPs

Column 2: 2021-12_DNS

Column 3: 2022-01_IPs

Column 4: 2022-01_DNS 

Column 5: Desired Output

Column 1 has the IP Addresses for the previous month, so:

1.2.3.4

1.2.3.5

1.2.3.6

Column 3 has new IP Addresses from the current month along with some constants from the previous month:

1.2.3.4

1.2.3.6

1.2.3.9

Columns 2 and 4 have data not exactly relevant to what I'm trying to accomplish in Column 5.

[freginier]

With Power Query, create a merge in the same table with IP & DATE criteria 

With DAX, create a calculated column to compare the date IP where IP match  : 

CALCULATE ( MAX ( DATE), filter( TABLE, [IP] = EARLIER([IP]) )

[austinjgreer]

I apologize, would you be able to show me an example with a screenshot? I'm having a hard time understanding. Still pretty new to this! Thank you for your response.

[freginier · Accepted solution]

My bad I don't see you have IP on 2 columns 

old_new = 
var searchIP = LOOKUPVALUE('Table'[prev],'Table'[prev],'Table'[current])
return 
IF( searchIP = BLANK(), "new","old")

The result

[austinjgreer]

Awesome! This did exactly what I needed. Thank you so much!

[freginier]

You'r welcome 🙂