Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Multiple Many-to-Many Relationship

Hello all,   I am trying to create or simulate 2 many-to-many relationships with my data tables so that I can filter the visualizations properly.   Here is simplified version of my problem:   T...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

I want to use the 2nd table to show the questions and respective score filtered by location and by year.  This will appear in an accompanying visualization (ie. a table)

Hi  Anonymous,

 

If you only want to get the same data as Table 1 - Overall Score from Table 2 - Question Scores, you needn't to build relationship between those two tables. You can create a new table use Summarize() function like below( But the prerequisite is that you need to know how to calculate the total Score ):

 

Table = SUMMARIZE('Question Scores','Question Scores'[Year],'Question Scores'[Location],"Total",SUM('Question Scores'[Score]))

 

Also you can drag fields from the Table 2 - Question Scores into matrix visual like below:

 

 

Best Regards,
Qiuyun Yu