Avoiding Many to Many Relationships in Data Model

I have two fact tables (Enrollments & Renewals) and want to able get information from both tables into visualizations. To avoid many to many relationships, I created an intermediary (third) table (Agent) with a unique key column (NPN) from both fact tables. The relationships between the three tables are straight forward, Agent Table works as intermediate between Enrollments & Renewals (one to Many by NPN). However, I can only show NPN for Agent Table by Enrollments and Agent Table by Renewals but showing NPN from all three tables causes error “there is no relationships between Enrollments & Renewals”. I thought to avoid Many to Many relationships, I created the Agent Table as intermediate table. Any thoughts? here is the link to PBIX:

Thank you,

Hi @Helal ,

Quick answer :
You are dealing with lookup (or common dimension) table Agent and 2 Fact tables Enrollments & Renewals

More about table types you can find at:


And you have relationships between them (look at the arrow side )

Direction → you can filter from Agent to Enrollments

And from Agent to Renewals

More about relationships you can find at:


So you can use Agent NPN (from Agent table) and measures from Enrollments & Renewals.

I suppose you want to see which Agent participate in Enrollment & Renewals

  • you can use the trick

For_Helal.pbix (1.6 MB)

or you can create indicator instead.

Hope it helps.

Good luck

Thank you so much for quick response. It all worked great. The Agent table is a lookup table that connects to both Enrollments and Renewals by NPN to avoid many to many relationships. Actually, each agent has a number of subscribers that I am tracking month by month. Too, I am creating top perofmer dashboard based on Agent’s recruiting activities. Thank you again for your help.


1 Like

@Helal - I am glad that I can help.
When I start with PBI I have similar questions. Luckily I had someone to ask.

Good luck with your analysis.