9. Creating Product HierarchyFor 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/https://powerbitraining.com.au/data-model-common-issues-and-solution-10-power-bi-training-in-sydney-power-bi-training-in-melbourne/https://powerbitraining.com.au/data-model-common-issues-and-solution-11-power-bi-training-in-sydney-power-bi-training-in-melbourne/https://powerbitraining.com.au/working-with-multiple-fact-tables-power-bi-training-in-sydney-power-bi-training-in-melbourne/ https://powerbitraining.com.au/dealing-with-misleading-interpretations-power-bi-training-in-sydney-power-bi-training-in-melbourne/
14. Creating Bridge TableFor a step-by-step guide on this: https://powerbitraining.com.au/creating-bridge-table/
- 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.
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.