one record in table one associated with all records in table 2 in Power Povit

[PengB]

 Hi guys,

Below are a sample tables I use to ask the question that happens in a bigger file. After I created relationship in Power Pivot using table 1 and table 2, I created a Pivot Table. But why each ASIN in Table 1 is associated with all ASINs in Table 2? Shouldn't it be one ASIN in Table 1 associated to one ASIN in Table 2?  Thanks for your help!

Table 1: 

ASIN	Product Name	Category
B001	Vacuum Cleaner	Cleaning
B002	Steam Mop	Cleaning
B003	Air Purifier	Air Care
B004	Robot Vacuum	Cleaning
B005	Handheld Vacuum	Cleaning

Table 2: 

ASIN	Revenue	Units Sold
B001	10000	100
B002	8000	80
B003	12000	120
B004	15000	150
B005	5000	50
B006	7000	70

Relationship in Power Pivot:

Now the Pivot Table looks like this:

Why it doesn't look like this:?

ASIN	ASIN	Product Name
B001	B001	Vacuum Cleaner
B002	B002	Steam Mop
B003	B003	Air Purifier
B004	B004	Robot Vacuum
B005	B005	Handheld Vacuum
(blank)	B001	(blank)
	B002	(blank)
	B003	(blank)
	B004	(blank)
	B005	(blank)
	B006	(blank)

[Ray_Minds]

Hi  To understand the exact issue which you are facing, I tried to create the scenario in Power BI. 
 
Step –1: Create two tables like below in Power BI (Enter data option and paste the below tables) 

 
Table –1 
 

ASIN	Product Name	Category
B001	Vacuum Cleaner	Cleaning
B002	Steam Mop	Cleaning
B003	Air Purifier	Air Care
B004	Robot Vacuum	Cleaning
B005	Handheld Vacuum	Cleaning    Table –2    ASIN  Revenue  Units Sold  B001  10000  100  B002  8000  80  B003  12000  120  B004  15000  150  B005  5000  50    Step –2 Create relationship in Model view       Step –3 Represent the data in a matrix visual    In above matrix visual, the functionality is working as you are expecting. (one ASIN in Table 1 associated to one ASIN in Table 2, not each ASIN in Table 1 is associated with all ASINs in Table 2).    Try implementing the solution in Power BI and then you can export the results in excel by clicking on ellipses and export button.    If the provided steps have addressed your concern, kindly mark this post as the solution to assist other members who may encounter a similar issue.

@PengB

[PengB]

Thank you for your detailed response. Although you didn't give me the answer to my question about Power Pivot, you did provide a good solution in Power BI. 

[Ashish_Mathur]

Hi,

Do not drag ASIN from Table 2.  Also, drag Units sold to the values well.

[PengB]

Hi Ashish,

Thank you for your reply. Why can't I drag ASIN from Table 2? Sometimes I need to check if there are any ASINs that are in Table 2 but not in Table 1. Yes, looks like dragging Units sold to the values solve the problem, but what the reasons behind? 

Thanks,

[Ashish_Mathur · Accepted solution]

Hi,

The Grouping in any visual should always happen by the fields (columns) in the dimension tables (not the fact tables)

[PengB]

Now I got it. Thank you Ashish! 

[samratpbi]

Hi,

Have you defined the relationship between the tables? Seems the relationship is not defined properly.

 in the Model view. Please check once.

If this helps to resolve your issue, then please mark it as solution provided, Thanks!
Samrat

[PengB]

Hi Samrat,

Thank you for your quick response. I've defined the relationship. Check this screenshot.