Getting around many-to-many relationship

[btreg]

I have two tables of data in my Data Model:

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

 

[Matt Allington]

I suggest you start with my blog post here and see if that helps you. Many to Many Relationships in DAX Explained - Excelerator BI

ironically i have been working on my presentation for Difinity 2017 all day on this exact topic

Difinity 2017 Sessions