Forum Discussion
Multiple Many-to-Many Relationship
I have tried using a Year table of distinct Year values and a Location table of distinct Location values and relate these to both of my tables, but alas it said it would create ambinguity. I can only get it to work with one or the other.
I will play with USERELATIONSHIP and see if this works.
I actually did come up with a workaround though... I concatenated Year and Location in both tables and this allowed me to create a one to many relationship. The visualization now filters for both location and year. For my purposes this works so I will probably just run with it.
Merging in Total Score to Table 2 was something I was considered, and perhaps it's more elegant than the one I listed.
Either way, thank you for your response.
You can specify lookup tables for Year and Location linked to both Overall Score and Question Scores, if you use 'Cross filter direction' of Single rather than (the default) of Both in the relationships from those lookup tables.
Filters on Location and Year should then work fine against the two fact tables.