Multiple Many-to-Many Relationship

[Anonymous]

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:

Table 1 - Overall Score

Table 2 - Question Scores

I am using the first table to show the overall scores for a given Location over time (year by year), and another visualization to show each locations overall score for a given year.

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)

I need to create a relationship between Table1[year] and Table2[year], AND a relationship between Table1[Location] and Table2[Location].  As you can see, both are many-to-many relationships.

With a bridge table, I can easily create one of those relationships and my visualizations adjusts according to the filter.  I tried to create two bridge tables, but it failed miserably.  I am new to PBI and DAX...

Please note that I cannot derive the overall score from Table2.  The algorithm is not a straightforward average and I am not privy to the calulcate method used.

[v-qiuyu-msft]

---

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

[Anonymous]

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.

[Greg_Deckler]

You should be able to creae a Year table of distinct Year values and a Location table of distinct Location values and relate these to both of your tables. You may have to use the USERELATIONSHIP function for some calculations if you do it this way. You might also consider just doing a join in "M" to merge in your Total Score. Yes it would be repeated but you could always use something like MAX to get a "single" value.

[Anonymous · Accepted solution]

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.