Relationship not picking up

[Anonymous]

I have this data:

 

The table on the left uses what was an attempt to consolidate all names together and the table on the right uses the original values before consilidating. Both tables use this formula:

So as you can see, I was intending to use the ALL function so the number is the same for everybody as a group. It works correctly on my table on the right, but for some reason it does not work on my table on the left, despite the fact that I have relationships established between the 2 datasets:

So the 2 tables in the middle were created to consolidate everyone's names and establish the necessary relationships. My [LQ NPS] formula comes from my NPS Consolidated table. The NPS Consolidated (2) table is used as a bridge between NPS Consolidated and Benefits AM. So with these relationships established, I assumed my [LQ NPS] would work how I'd like to but it's not. How can I get the [LQ NPS] to return the same value for all Benefits AM's?

[v-huizhn-msft]

Hi @Anonymous,

For your  [LQ NPS] formula, ALL function clear all filters of table NPS Consolidated. Please try the following formula for all Benefits AM's and check if it works fine.

LQ NPS1=CALCULATE([Last Quarter NPS],ALL('NPS Comsolidated'),ALL('Benefits AM'))

In addition,  you use NPS Consolidated (2) table as a bridge between NPS Consolidated and Benefits AM, you can't create relationship by using 'NPS Consolidated' in your scenario?

Best Regards,
Angelia