Relationship Query

[it]

I'm new to Power BI and trying to figure out how to utilise relationship data between 2 csv's and would appreciate it if someone here can assist.

Example:

I have a CSV file with computer IP addresses on them, eg 192.168.1.101 and another CSV of all the subnets that we have, eg Office: Australia, Subnet: 192.168.1.0/24

Query:

Is there a way for me to link the 2 csv's together and have Power BI display which Office the computer belongs to?

[Greg_Deckler]

So, yes. What you would want to do would be to create a calculated column on your IP addresses table that determines that IP addresses' subnet (even better if it is already there) then you could relate the two tables. Sample data would help tremendously.

[it]

Hi smoupre

Thanks for your reply.

Example: 

Office	Subnet
NSW	10.10.0.0/24
QLD	10.20.0.0/24
VIC	10.30.0.0/24

Description	Serial	IP
Desktop	1234N123	10.20.0.50
Laptop	2345M678	10.10.0.108
Server	3456O789	10.30.0.167

So what I would like to achieve as an end result is link both tables in 2 different CSV files together and have Power BI show the following table based by the IP in the second table and automatically populate the Office from the first table. Hope i'm making sense 🙂

Description	Serial	IP	Office
Desktop	1234N123	10.20.0.50
Laptop	2345M678	10.10.0.108
Server	3456O789	10.30.0.167

[Anonymous · Accepted solution]

A really quick solution would be a calculated column in your computer table:

Subnet = LEFT([IP], 5) & ".0.0/24")

Now go to your table relationships tab and link your computers to your locations via the subnet.

Now add the matrix visual to your reports and you'll see if you list the computers, then if you bring in your Office field from your office list, it will correctly show.