Filtering Issue when using Relationships in Data Model

Question

I'm currently trying to create a report with data pulled from two sharepoint lists.  The lists are linked through a field called Select Project ID.  I have created a relationship using data model in excel between the two tables - it is a 1 to many relationship.

When I start pulling the appropriate columns from the two tables into rows in the pivot, it's creating duplicate entries

How I would assume the pivot would work is demonstrated in the first image below:  Pull in the Project Name Column from table 1 or table 2, Pull in the Sub Projects associated to the Project Name in table 2(the many relationships), and when I pull in the duration column from table 1 - it should only pull in the duration for that corresponding project row.

Project Name (Select Project ID)	Sub Projects	Project Duration
Project 1	Sub Project 1	1 Month
	Sub Project 2
	Sub Project 3
Project 2	Sub Project 1	2 Months
Project 3	Sub Project 1	3 Months

How it is actually working is shown below:  Pull in the Project Name Column from table 1 or table 2, Pull in the Sub Projects associated to the Project Name in table 2(the many relationships), and when I pull in the duration column from table 1 - it pulls in the duration for every project for that client and repeats them for every project.

Project Name (Select Project ID)	Sub Projects	Project Duration
Project 1	Sub Project 1	1 Month
	Sub Project 2	2 Months
	Sub Project 3	3 Months
Project 2	Sub Project 1	1 Months
		2 Months
		3 Months
Project 3	Sub Project 1	1 Months
		2 Months
		3 Months

I tried slicing the information and got the same results.  Is there a way to create this report so that it isn't duplicating information when pulling from two lists/tables?

Answer 1

You are welcome.  If my reply helped, please mark it as Answer.  Please clarify your question.

Answer 2

Below is the link with an example:

https://onedrive.live.com/redir?resid=65AB4CBADABE3B7B!107&authkey=!AKzKGZK5reOSAmI&ithint=file%2cxlsx

I included a screen shot of sharepoint as well.  the example I have shown is the project duration.  I would assume with the relationship of the tables, that each project would only have 1 duration, the one specified in sharepoint.  However, all project durations repeat for all projects.

Thanks,

Kate

Answer 3

Hi,

Please create dummy data and then share the workbook.

Answer 4

The workbook has sensitive information in it, so I can't post it.  Is there additional information or details I can provide that would be helpful?

Thanks,

Kate

Answer 5

Hi,

Upload the workbook to OneDrive and share the link of the workbook here.