Power BI

Circular References in Data Modeling

In our recent Power BI online training held in Sydney, one of the attendees accidently created a circular referenced data model while solving a common business problem. This created an ambiguity in the data set. This problem was addressed by our trainer during the online Power BI training immediately by 2 methods discussed in this blog. Here is the link to the blog series covering all such problems: https://powerbitraining.com.au/category/data-modeling-issues-and-solutions/ Before we dive into details of the problem at hand, here is a list of topics covered so far:

1. Importing Data

For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

2. Data Preparation

For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

3. Creating Date Table

For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-3-power-bi-training-in-sydney-power-bi-training-in-melbourne/

4. Setting Model Design

For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/

5. Creating Surrogate Key

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/

6. Merging Queries

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-6-power-bi-training-in-sydney-power-bi-training-in-melbourne/

7. Creating Calculated Columns

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-7-power-bi-training-in-sydney-power-bi-training-in-melbourne/

8. Flattening the Product Table

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-8-power-bi-training-in-sydney-power-bi-training-in-melbourne/

 9. Creating Product Hierarchy

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-9-power-bi-training-in-sydney-power-bi-training-in-melbourne/

10. Active vs. Inactive Relationship

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-10-power-bi-training-in-sydney-power-bi-training-in-melbourne/

11. Working with Role Playing Dimensions

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-11-power-bi-training-in-sydney-power-bi-training-in-melbourne/

12. Adding Higher Levels of Details

For a step-by-step guide on this: https://powerbitraining.com.au/working-with-multiple-fact-tables-power-bi-training-in-sydney-power-bi-training-in-melbourne/

13. Cater for Misleading Interpretations

For a step-by-step guide on this:  https://powerbitraining.com.au/dealing-with-misleading-interpretations-power-bi-training-in-sydney-power-bi-training-in-melbourne/

14. Creating Bridge Table

For a step-by-step guide on this: https://powerbitraining.com.au/creating-bridge-table/

15. Circular References

Circular References are avoided in the data model because they create ambiguity. Let’s find an appropriate solution to situations where circular references are created. However, lets analyze our model for the following business questions.
  • Q1: How well is each sales person performing with respect to his sales contribution.
  • Q2: How well is each sales person performing with respect to the product assigned to him/her.
Power BI online training

Let’s consider the first question.

Q1: How well is each sales person performing with respect to his sales contribution.

To answer the above question, the filter propagates from the Sales Person table to the Sales Table and the values are fetched

Power BI online training

Now let’s answer the second question:

Q2: How well is each sales person performing with respect to the product assigned to him/her.

To answer this question, filter propagates from Sales Person Table to Sales Person To Product ID Table but cannot propagate any further.

Power BI online training

To find a Sales Person’s performance w.r.t. to the product assigned to him/her.

1. Click on the line between Product Type and Sales Person To Product Type

2From the Cross filtering menu, click on Both.

The filter can now propagate in the Product dimension direction. However, a circular reference has been created. This introduces ambiguity in the model.

Power BI online training

Once the record for a sales Person is fetched, it is not clear what the performance results will show. Sales Person is therefore a Role Playing Dimension.

3. Click on Report Tab in the Left Ribbon.

4. Click on Modeling Tab in the Ribbon Menu.

5. Click on New table.

6. Type the following DAX expression in the Formula Bar.

Power BI online training

A copy of Sales Person dimension table has been created.

7. Click on Model in the Left Ribbon.

8. Right click on the line between Sales Person Table and the Sales Person To Product Type

9. Click on Delete.

Power BI online training

10. Now click on Person ID in the Sales Person To Product Type Table and drag to Sales Person Product Performance Table.

Power BI online training

The Important take-away from this exercise are:

  • Bi-directional filtering should only be used in cases where dimensions terminate.
  • To avoid ambiguity in the model, Roles Playing Dimensions must be replicated according to the need.

Conclusion:

Circular references are often created in data model to solve certain business problems.  These circular references create ambiguity in the data model. Therefore, they must be avoided by using bi-directional filtering only where dimensions terminate or duplicating role playing dimensions wherever required.

More Checkouts for Power BI Training in Australia.

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *