IP address to locational subnets

[Genfuzuki]

Hi, apologies as i'm a bit of a noob when it comes to PowerBi and Copilot suggestions either error or don't exist in PowerBI.

I have an Intune device table and Entra sign-in table which are succesfully in a relationship (and syncing) so i can get device name, user name, sign-in times etc from across the two tables. The intention is to show a dashboard of office locations (approx 14)  with the users that are signed in to that location using thier IP address. Each office location has a different subnet so i was looking to create a new table with 'Office location', 'Start IP' and 'End IP' in the hope that the indivdual device IP address from the Intune log could match the location in the new table and i could use a map.

I can get this working with thier external IP as it is only a single external IP per location but would rather have thier internal IP (as it is better for support purposes). That is as far as i have got and can't figure out what i need to do next so any pointers would be helpful please ?

[v-menakakota]

Hi @Genfuzuki ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank  @johnt75 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
Also, keeping the office subnet ranges clean and non-overlapping avoids accidental multiple matches.

Best Regards, 
Menaka
Community Support Team 

[johnt75 · Accepted solution]

One approach would be to use Power Query to split the start and end ranges, and user IP, into their constituent parts. I'm guessing that you don't need the last part, e.g. if the IP address was 192.168.5.4 you would only need 192, 168 and 5 as separate columns.

Use Power Query to add in index column to the office table, unless it already has a unique ID in which case you can use that.

Add a calculated column to the table which has the user IP address like

Office Index =
SELECTCOLUMNS (
    FILTER (
        Offices,
        Offices[Start IP1] <= 'Table'[IP1]
            && Offices[End IP1] >= 'Table'[IP1]
            && Offices[Start IP2] <= 'Table'[IP2]
            && Offices[End IP2] >= 'Table'[IP2]
            && Offices[Start IP3] <= 'Table'[IP3]
            && Offices[End IP3] >= 'Table'[IP3]
    ),
    "@val", Offices[Index]
)

It would also be possible to create this column using Power Query if necessary.

You can now link the office table to the table with the IP addresses using the index column.