Forum Discussion
Different relationship levels
Hello,
I am having a particular difficulty trying to create a relationship between 3 table. In this case, one of this tables have different levels of hierarchy in the same column.
1. 'factNPS'
My Net Promoter Score fact table.
2. 'dimLines'
Bus lines data. Example:
| LineID | BusDepot | Superintendence | etc. |
| 1 | GCG | ES | |
| 2 | GCG | ES | |
| 3 | SAM | ES | |
| 4 | COL | ES | |
| 5 | RIO | ES | |
| 6 | XPT | BA | |
| 7 | SSA | BA | |
| 8 | XPT | BA |
3 'dimManagers'
Shows me the managers of each place.
| Name | Department | ID | Role | etc. |
| Jon | GCG | |||
| Kevin | SAM | |||
| Kent | COL |
|
|
|
| Duane | RIO |
|
|
|
| Mel | XPT |
|
|
|
| Mike | SSA |
|
|
|
| James | BA |
|
|
|
| Alex | ES |
|
|
|
It is pretty easy to connect my 'dimLines' table to 'fatNPS', because there is a [LineID] column in 'fatNPS' table, so I am able to calculate the Net Promote Score for each Depot or Superintendence.
However I have no idea how to connect my 'dimManagers' to 'dimLines' and/or 'fatNPS' tables to get the NPS for each manager, since [Department] column have different levels of hierarchy.
PS: our managers are responsibles for several KPIs and NPS is only one of them. I also have to connect all others KPIs to each manager.
How could I do this?
Thanks for help.
Hi Anonymous ,
1. There is no relationship between dim Managers and dim Lines.
2. Then we can create two column in dim Lines to calculate the managers’ name.
Column = CALCULATE(MAX(dimManagers[Name]),FILTER(dimManagers,dimManagers[Department]=dimLines[BusDepot]))Column 2 = CALCULATE(MAX(dimManagers[Name]),FILTER(dimManagers,dimManagers[Department]=dimLines[Superintendence]))And we create a matrix, the result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared.
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Well, you could create 2 relationships and use USERELATIONSHIP. Otherwise, you will likely have to form the "relationship" within any measure you create.
Hi Anonymous ,
1. There is no relationship between dim Managers and dim Lines.
2. Then we can create two column in dim Lines to calculate the managers’ name.
Column = CALCULATE(MAX(dimManagers[Name]),FILTER(dimManagers,dimManagers[Department]=dimLines[BusDepot]))Column 2 = CALCULATE(MAX(dimManagers[Name]),FILTER(dimManagers,dimManagers[Department]=dimLines[Superintendence]))And we create a matrix, the result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared.
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
