Pivot table data model relationships not working

[Dan02]

Hello all,

I'm fairly new to creating relationships and data models. I'd like to use a single pivot table to examine two tables of data. However, whenever I do it I find that the pivot table results work fine if I'm examining values from my primary table, as grouped by categories in my secondary table. However, when I try to do the opposite, looking at values in my secondary table using categories specified in my primary table, it doesn't work and I get a message indicating that "Relationships between the tables may be needed". Is there something I'm misunderstanding about how this should work?

Many thanks!

 

[Dan02]

To add a bit more context, I'm inserting some images below. Here's some example data...

I've of course created a relationship with the ID variable...

When I want to look at values from Table 1 using a category from Table 2, everything seems fine...

But when I then want to look at values from Table 2 using a category from Table 1, things go wrong...

Please let me know if you have an idea as to why this is happening. Thanks!