Show products by supplier

Hi,

I have connected Power Pivot to two tables (in the data model).
Table 1 = Suppliers with Supplier ID as primary key
Table 2 = Products with Supplier ID as foreign key

I connect the tables in Power Pivot on the key fields.

In The pivot table, I add the Supplier Key and Supplier name to the Rows Area
Then I add the Product Name from the Products Table to the Area field

What I get is ALL products showing for ALL suppliers. (I was expecting to see all suppliers with only the products they sell - due to the link)

Is this supposed to happen?
How can I only see relevant products per supplier in the pivot?

TX

Attached is the sample workbook.

View attachment 3415

I think I found out that I need to use the RELATED() function. So effectively one needs to pull in all fields into one of the tables and pivot only on that one table?! Please inform if I am wrong!! I was under the impression that I can (since I have a defined link) pull fields from both tables into the pivot and it will display related to the key fields.

I look forward to any feedback!
TX

Excel 2013 Pro Plus with PowerPivot.
What you got is expected behavior if you leave the Values field blank.
Supplier ID in the Row field should come from the Products table.
Otherwise you are wrong on all counts.
Since PowerPivot is non-intuitive, a book might help.
http://www.mediafire.com/view/jj992p53oqwwz7j/04_23_15.xlsx

TX Herbds7
You've confirmed a few good things for me.