Forum Discussion
Relationship Query
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?
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.
3 Replies
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.
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 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.
