Forum Discussion
How to join multiple SharePoint lists in Power Pivot
Hi,
I have the following SharePoint list structure. I am trying to make a PowerPivot report that will display some data. I have already added these lists/tables in Power Pivot and created the relationship between tables. But there is some data which I don't how to display.
EmployeeTable - Master
EmployeeID (this is text code for e.g. 001, 002)
EmployeeName
LeaveTable - Child
EmployeeID
LeaveType (this is text code for e.g. 01, 02)
NoOfLeavesTaken (this will be number of days that person was absent for e.g. 5, 8, 17)
LeaveAvailableTable - Child
EmployeeID
LeaveType (same as above)
LeaveTypeTitle (this is text field for e.g. Annual, Casual)
TotalLeavesAvailable (this will be number of leaves available for e.g. 24, 10)
EmployeeTable is a master table which contains one record for each employee. LeaveTable is a child table which contains multiple records for each employee for e.g.
001 01 8
001 01 5
002 02 11
In the table above, the first column is EmployeeID while second is LeaveType and third is NoOfLeavesTaken.
LeaveAvailableTable is a child table contains multiple records for each employee for e.g.
001 01 Annual 23
001 03 Casual 10
002 01 Annual 12
002 02 Medical 7
I want to display the following report:
EmployeeID, EmployeeName, LeaveTypeTitle, TotalLeavesAvailable, NoOfLeavesTaken
So basically Employee 001 may have taken 8 Annual Leaves at one time and then again took 5 annual leaves at one time which brings the total to 13. Now his available Annual Leave is 23 so report will become something like this:
001, Some Employee, Annual, 23, 13
I have already joined EmployeeTable with LeaveTable through EmployeeID and also joined EmployeeTable with LeaveAvailableTable through EmployeeID. Problem is I cannot join LeaveTable with LeaveAvailableTable because they both contain multiple values and Excel doesn't allow it.
Is there a way to make this report?
8 Replies
Just to summarize, there are 3 tables (one master and two child). Both child are joined to master by EmployeeID.
Master contains one record for each employee while child table contains multiple records for each employee.
I want to display one record from Master and then sum of records from both child tables in next two columns.
Hi frankmartin,
You can create a measure below:
NoOfLeavesTaken01 = CALCULATE(SUM(LeaveTable[NoOfLeavesTaken]),FILTER('LeaveAvailableTable','LeaveAvailableTable'[EmployeeID]=MAX('EmployeeTable'[EmployeeID]) && 'LeaveAvailableTable'[LeaveType ]=MAX(LeaveTable[LeaveType ])))
You can see attached pbix file.
Best Regards,
Qiuyun YuI modified DAX a little bit because you are using MAX function for EmployeeID which is NUMERIC but in my case it is STRING. So using MAX function was throwing error that this field must be NUMERIC.
But after changing it, I am getting another error
A single value for column 'EmployeeID' in table 'EmployeeTable' cannot be determined. This can happen when measure formula refers to a column that contains many values without specifying aggregation such as min, max, count, or sum to get a single result.
This error is strange because 'EmployeeTable' is master table and contain exactly one record for each employee and not multiple.
Hi frankmartin,
Please use Max() function to aggregate the 'EmployeeTable'[EmployeeID] as suggested in my previous post:
NoOfLeavesTaken01 = CALCULATE(SUM(LeaveTable[NoOfLeavesTaken]),FILTER('LeaveAvailableTable','LeaveAvailableTable'[EmployeeID]=MAX('EmployeeTable'[EmployeeID]) && 'LeaveAvailableTable'[LeaveType ]=MAX(LeaveTable[LeaveType ])))
Best Regards,
Qiuyun Yu
