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: https://drive.google.com/file/d/179vpZ70fKslU-VekV5GMVF6ccHALS3Ht/view?usp=sharing
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.
