I have two tables of data in my Data Model:
Table 1 Field Names
<tbody>
</tbody>
Table 2 Field Names
<tbody>
</tbody>
I want to create a Pivot Table that shows:
Activity Name (Row Heading)
Scaling Factor Name (Row Heading)
Faculty (Column Heading)
Scaling Factor Value (Values)
The link between the two tables is the 'Scaling Factor Name' field. However, there are multiple instances of the same values in both tables, so I can't create a relationship directly between them as this would be a many-to-many relationshipo which PowerPivot can't handle. I am aware that I can create a separate table of the unique Scaling Factor Names and link this table to both Table 1 and Table 2, however, I am struggling with how I then write a suitable measure to pick up the correct Scaling Factor Values.
Any help much appreciated.
Thanks in advance.
Ben
Table 1 Field Names
Faculty Name |
Scaling Factor Name |
Scaling Factor Value |
<tbody>
</tbody>
Table 2 Field Names
Activity Name |
Scaling Factor Name |
<tbody>
</tbody>
I want to create a Pivot Table that shows:
Activity Name (Row Heading)
Scaling Factor Name (Row Heading)
Faculty (Column Heading)
Scaling Factor Value (Values)
The link between the two tables is the 'Scaling Factor Name' field. However, there are multiple instances of the same values in both tables, so I can't create a relationship directly between them as this would be a many-to-many relationshipo which PowerPivot can't handle. I am aware that I can create a separate table of the unique Scaling Factor Names and link this table to both Table 1 and Table 2, however, I am struggling with how I then write a suitable measure to pick up the correct Scaling Factor Values.
Any help much appreciated.
Thanks in advance.
Ben