Forum Discussion

austinjgreer's avatar
austinjgreer
Frequent Visitor
4 years ago
Solved

IP Address Compare and Categorize

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's avatar
    freginier
    4 years ago

    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