Creating Table Relationships

Tables that OpenGround imports into Power BI are stand-alone and have no knowledge of, or relationship to, the other tables in the report. 

This can cause problems when trying to get tables to work together - For example, if you have two tables in a report which have a Location ID column and you use Location ID from one table in a filter Visual, then the filter will only apply to the table you selected the field from and won't filter the other tables.

To define relationships between the tables you need to follow the steps below:-

• View the Relationship view by clicking the icon on the left-hand side of the screen.
• If the tables are not shown in the view, you can click and drag them from the right-hand list.
• Select the field in the first table and click and drag it to the field you want to join it to in the second table.  This creates a one-to-many relationship from the first field to the one you dragged it to.  In the example below the LocationDetails.Location field was dragged to the Location ID field in the Sample table.  This means that each Location ID has many samples.

The majority of relationships that you create in your report will be one-to-many and can be set up using the method above.  However, you can select the line between the tables and right click to edit the relationship using the form shown below

Important Note:

Power BI only allows you to create a relationship using one field from each table.  If you need to join by more than one (for example lab test results linked to samples) then you will need to create a new column in the table and concatenate the fields to create a key field combination column.  How to do this is described in the Create New Column section.

Tables cannot be joined to themselves.  However a duplicate table can be created, and the two tables can be joined.  This technique is used to find non continuous geology.