Data Model relationship not considered. Why

[minimaster]

Two tables are loaded to the Data model and a relationship has been created for the Name column. In the pivot the value sum always show the total value of all names in the Name-Value table and the relationships with the 1st table is being ignored. Why is this and how can we adjust to respect the relationship between the two tables?

Data Model Problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Date	Location		Name	Value		Sum of Value	Column Labels
2	A	01/01/2020	Hamburg		A	4		Row Labels	01/01/2020	01/04/2020	Grand Total
3	B	01/01/2020	Hamburg		B	9		Berlin
4	C	01/01/2020	Berlin		C	13		B		71	71
5	D	01/01/2020	Berlin		D	3		C	71		71
6	E	01/01/2020	Paris		E	15		D	71		71
7	F	01/01/2020	Paris		F	27		E		71	71
8	A	01/04/2020	Paris		Total	71		F		71	71
9	B	01/04/2020	Berlin					Hamburg
10	C	01/04/2020	Hamburg					A	71		71
11	D	01/04/2020	Hamburg					B	71		71
12	E	01/04/2020	Berlin					C		71	71
13	F	01/04/2020	Berlin					D		71	71
14								Paris
15								A		71	71
16								E	71		71
17								F	71		71
18								Grand Total	71	71	71
Sheet1

 

[RoryA]

Your data layout doesn't appear to make a lot of sense. How does the 4 for Name A relate to location or date, for example?

 

[minimaster]

It makes sense when you like to know how much value was in a location at a certain date. Or?

 

[sandy666]

Could you show what you want to achieve?

 

[RoryA]

It makes sense when you like to know how much value was in a location at a certain date. Or?

You've got two different dates and locations for Name A for example. How should the 4 relate to them? 4 for both? Normally the one side of your 1 to many relationship would not contain the values.

 

[minimaster]

Sandy666, your pivot shows the desired result. I know it is achievable with a lookup in the table with the dates/locations, or with a table constructed with Power Query. That works I know. The question is why does this not work within the Data model and setting up relationships

 

[minimaster]

Normally the one side of your 1 to many relationship would not contain the values.

Is this restriction of the data model anywhere documented or is that just one of these "features" MS has build into Excel?

 

[RoryA]

It's basic star schema design - dimension vs fact tables. Relationships in the data model are more about the context in which filters are propagated rather than relationships as you might think of them from databases (SQL, Access etc). If you used the Name field from the second table in your pivot, you would see the values you want, but they would appear the same regardless of location or date as those filters are not propagated to the table you are using for the values.

I've never been terribly good at explaining this (which probably means I don't really understand it) so hopefully someone else will chime in with a clearer explanation!

 

[sandy666]

Sandy666, your pivot shows the desired result. I know it is achievable with a lookup in the table with the dates/locations, or with a table constructed with Power Query. That works I know. The question is why does this not work within the Data model and setting up relationships

that is why there is Power Query

 

[minimaster]

Ok thank you both. Conclusion: When using the Excel data model any data values that should be used for aggregations must be in the "many" table.