Forum Discussion
Manage Relationship across multiple tables
Hi,
I have tables with relationships like in the picture.
Both 'country' and 'Total Monthly SO' are connected to 'SN Data' through [country code], which is unique in 'country' but many in the other two. 'Fiscal Calendar' has one to many relationship with 'SN Data' through [Calendar Date] <-> [Date].
The 'Total Monthly SO' looks something like this:
I trying to create 2 matrix visualizations.
- The first one has 'country'[Country] as row, 'Fiscal Calendar'[Calendar Month] as column and Count('SN Data'[PD Rootcause]) as value. This one works corrrectly.
- The second one has 'country'[Country] as row, 'Fiscal Calendar'[Calendar Month] as column and Sum('Total Monthly SO'[total SO] as value. This one doesn't work. It only shows sum of total of a particular country in every month, which looks something like this:
What could I do to fix this? Creating duplicated Dimension Table or showing the matrix separately are not options. Because later on I will need to create another matrix with 'country'[Country] as row, 'Fiscal Calendar'[Calendar Month] and Divide(Count('SN Data'[PD Rootcause]), Sum('Total Monthly SO'[total SO]) as value.
Thanks,
I would have expected the relationships to be set up as follows:
- Fiscal Calendar set up as one-to-many with SN Data as you have it
- Fiscal Calendar set up as one-to-many with Total Monthly SO (probably split Month Year in this table and connecting on the Month field)
- country table connected as one-to-many to both the Total Monthly SO and SN Data tables (I don't understand how it can be many-to-many as there is only one value for each country but maybe I'm missing something)
I think that would allow you to do everything you are looking for. Hope it helps...
Make sure you don't have blank(s) in the country table
5 Replies
- VijayP
Community Champion
Anonymous
merge Country and SN DAta Table with Left outer option (Left is SN Table and Right is Cuntry) to get single table to avoid many to many.
I would have expected the relationships to be set up as follows:
- Fiscal Calendar set up as one-to-many with SN Data as you have it
- Fiscal Calendar set up as one-to-many with Total Monthly SO (probably split Month Year in this table and connecting on the Month field)
- country table connected as one-to-many to both the Total Monthly SO and SN Data tables (I don't understand how it can be many-to-many as there is only one value for each country but maybe I'm missing something)
I think that would allow you to do everything you are looking for. Hope it helps...
Now that you mention, I think the problem stems from 'country'. Each of the country code is unique, I will try to figure out why it is many-to-many. Plus, I can have 2 active relationships from 'Fiscal Calendar' to both 'SN Data' and 'Total Monthly SO'. However, I can't do the same with 'country'. Do you have an idea where the problem is?
PaulDBrownMake sure you don't have blank(s) in the country table
