Pivot Table could not detect relationship but Diagram view has relationship

[INN]

Hi
I have 2 tables and I added them to data Model of Power Pivot. I went to Diagram View and then Dragged Name from the frist box and matched with the Employee Name in the second box, so a relationship 1-to Many has established. Please see below. Why when I create pivot table it says that could not detect relationship? The diagram view is showing I have a relationship? Thanks you very much.

Worksheet in week11.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	Gender	Department	Location	Phone#			Employee Name	Age	Salary
2	Judith Collingwood	Female	Management	New Jersey	1-227-803-3871			Judith Collingwood	51	$75,514
3	Lara Styles	Female	Sales	Idaho	2-240-511-3733			Lara Styles	41	$43,096
4	Gloria Vass	Female	Management	California	1-063-681-2743			Gloria Vass	38	$65,387
5	Matt Yates	Male	Marketing	Arizona	4-375-511-3060			Matt Yates	56	$116,185
6	Hayden Barclay	Male	Sales	Virginia	3-001-423-6756			Hayden Barclay	36	$111,150
7	Rosa Gardner	Female	IT	Nebraska	5-744-575-0520			Rosa Gardner	26	$98,529
8	Carter Dubois	Male	Operations	Maryland	3-211-804-8643			Carter Dubois	40	$49,778
9	Alan Richardson	Male	Finance	Connecticut	0-816-613-1802			Alan Richardson	33	$49,932
10	Jackeline Chapman	Female	Accounting	Oregon	2-646-560-5822			Jackeline Chapman	53	$76,402
11	Domenic Nayler	Male	Sales	Oklahoma	6-430-606-8001			Domenic Nayler	42	$77,921
12	Kimberly Funnell	Female	Research and Development	Wyoming	7-242-321-1781			Kimberly Funnell	18	$16,574
13	Aiden Hall	Male	Human Resources	Rhode Island	1-500-456-6567			Aiden Hall	60	$83,550
14	Gabriel Lakey	Male	Sales	Utah	3-624-321-7715			Gabriel Lakey	51	$102,954
15	Willow Holt	Female	IT	Alaska	4-471-266-5742			Willow Holt	36	$119,237
16	Drew Dunbar	Female	IT	Arizona	3-484-738-1631			Drew Dunbar	44	$25,361
17	Elle Ashley	Female	Finance	Wyoming	1-600-105-8467			Elle Ashley	18	$35,617
18	Valentina Widdows	Female	Research and Development	Delaware	8-363-618-8524			Valentina Widdows	42	$105,488
19	Rocco Miller	Male	Operations	Tennessee	1-251-243-4250			Rocco Miller	28	$53,658
20	Teagan Thompson	Female	Management	Colorado	3-426-351-1460			Teagan Thompson	26	$96,004
21	Sara Rossi	Female	Sales	New Mexico	7-211-682-7447			Sara Rossi	29	$97,499
22	Janelle Whatson	Female	Marketing	Wyoming	6-827-664-0662			Janelle Whatson	55	$79,176
23	Clint Beal	Male	Human Resources	Wyoming	5-114-658-7802			Clint Beal	44	$16,917
24	Shannon Vincent	Female	Management	Michigan	7-445-055-0810			Shannon Vincent	21	$79,967
25	Thea Bailey	Female	Sales	Alaska	1-705-832-8762			Thea Bailey	57	$16,781
26	Daria Harvey	Female	IT	Ohio	0-065-554-3738			Daria Harvey	41	$75,939
27	Noah Richards	Male	Management	Colorado	6-238-711-5273			Noah Richards	51	$84,884
28	Kamila Turner	Female	Marketing	Connecticut	4-202-514-1182			Kamila Turner	23	$17,023
29	Eduardo Dyson	Male	Operations	West Virginia	1-820-274-8361			Eduardo Dyson	58	$80,291
30	David Cox	Male	Research and Development	Oklahoma	8-533-107-6628			David Cox	54	$14,730
31	Mark Ryan	Male	IT	Arkansas	1-888-244-2243			Mark Ryan	65	$44,025
32	Miley Tyrrell	Female	Marketing	Oregon	3-712-084-3455			Miley Tyrrell	47	$113,552
33	Alba Durrant	Female	Operations	Rhode Island	7-188-607-0853			Alba Durrant	48	$44,845
34	Oliver Hunt	Male	Operations	Washington	1-147-183-5687			Oliver Hunt	25	$12,939
35	Javier Tennant	Male	Management	North Dakota	0-011-563-1020			Javier Tennant	27	$23,598
36	Jade Attwood	Female	Sales	Nebraska	0-001-514-2818			Jade Attwood	27	$69,731
37	Boris Murray	Male	Management	Michigan	2-530-431-6052			Boris Murray	30	$110,057
38	Bryce Davies	Female	Human Resources	Delaware	1-228-375-7553			Bryce Davies	33	$54,977
39	Jacob Cobb	Male	Human Resources	New Hampshire	0-336-458-3542			Jacob Cobb	28	$77,189
40	Crystal Patel	Female	Research and Development	Vermont	1-705-766-2777			Crystal Patel	35	$109,860
41	Samantha Stevens	Female	Sales	Vermont	5-485-653-4723			Samantha Stevens	47	$110,260
HR_1

 

[Skovgaard]

Hi,

I just tried, and it works fine I guess.
It looks like you dragged your relationship from HR_1 -> HR_2. It did the opposite.

/Skovgaard

 

[INN]

Thank you very much for your help. Yes I did from HR1 to HR2 because HR1 is the Fact table. The lookup table is HR2. So the 1-to-Many relationship should be the other way around, 1 to the HR2 and * to HR1, am I right? Thanks again.

 

[INN]

Sorry, I want to ask another question. When I let excel to detect the realtionship and create one, based on what excel will decide which one is the Fact table and which one is the lockup table? For this example, Excel will pick the HR2 as a fact table and HR1 as lookup table, which is not right I think. Thank you again.

 

[RoryA]

because HR1 is the Fact table

HR2 is the fact table, since that contains the metric you want to sum (Salary).

 

[INN]

Thank you very much. Never thought about that. Now it makes sense.