Creating relationships in Power BI Desktop

Null values, blank values, and duplicate values restrict you from forming associations when loading detailed data sets from various sources. For example, when we load data sets from active customer support requests as well as another data set of work items with schemas of:

CustomerIncidents: {IncidentID, CustomerName, IssueName, OpenedDate, Status} WorkItems: {WorkItemID, IncidentID, WorkItemName, OpenedDate, Status, CustomerName }

As a result, we can’t simply construct a link between these two data sets to monitor all occurrences and work items related to a single CustomerName. Because certain WorkItems aren’t associated with a CustomerName, that field will be left blank or NULL. For each given CustomerName, there might be several records in WorkItems and CustomerIncidents.

Steps for creating relationships in Power BI Desktop (with data having null or blank values)

Columns having null or blank values are common in data sets. However, this might cause problems when attempting to leverage relationships. As a result, you effectively have two alternatives for dealing with the problems.

• Firstly, you can remove the rows that have null or blank values.
• Secondly, you can do this using either the filter feature in the query tab or if in case you are merging queries, select the “keep only matching rows” option.

Furthermore, you may substitute null or blank values for values that function in relationships, such as “NULL” and “(Blank)”. Filtering out data at the query step does indeed delete records, affecting summary statistics and computations. The latter method retains the data rows, however, it may cause miscalculations in the model by making unconnected rows look linked. If you choose the latter option, make sure to apply filters in the View/Chart to get correct results. Most essential, determine which rows are preserved and which are eliminated, as well as the overall influence on the analysis.

Steps to create relationships in Power BI Desktop when the data has duplicate values

Duplicate data values restrict you from forming associations when loading comprehensive data sets from numerous sources. You can also get around this by establishing a dimension table that has unique values from both data sources. So, if we load data sets from active customer support requests and a data set of work items using the following schemas –

CustomerInicdents: {IncidentID, CustomerName, IssueName, OpenedDate, Status} WorkItems: {WorkItemID, IncidentID, WorkItemName, OpenedDate, Status, CustomerName }

We can’t just construct a link between these two data sets to monitor all events and work items related to a given CustomerName. Because certain WorkItems aren’t associated with a CustomerName, that field will be left blank or NULL. If the CustomerNames database has any null or blank values, you may still be unable to construct a relationship – see Creating relationships if my data contains null or blank values. For a single CustomerName, there might be several WorkItems and CustomerIncidents.

In order to establish a connection, we must first build a logical data set that contains all of the CustomerNames from both data sets. To build the logical data set, go to the Query tab and follow the steps below:

1. Firstly, duplicate both queries, naming the first Temp and the second CustomerNames.
2. Secondly, in each query, remove all columns except the CustomerName column
3. Thirdly, In each query, use Remove Duplicate.
4. Then, in the CustomerNames query, select the Append option in the ribbon, select the query Temp.
5. Lastly, in the CustomerNames query, select Remove Duplicates.

Then you will have a dimension table that you can use to relate to CustomerIncidents and WorkItems that contains all the values of each.

For More visits – Resolve inconsistencies by creating relationships in Power BI Desktop