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! 

Need 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

7 Replies

    • austinjgreer's avatar
      austinjgreer
      Frequent Visitor
      4 years ago

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

        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]) )