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.
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
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.
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
2. From 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.
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.
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.
10. Now click on Person ID in the Sales Person To Product Type Table and drag to Sales Person Product Performance Table.
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.
