How to join multiple SharePoint lists in Power Pivot

[frankmartin]

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?

[v-qiuyu-msft]

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 Yu

[frankmartin]

I 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.

[v-qiuyu-msft]

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

[frankmartin]

MAX function is not working because EmployeeID type is string and not numeric.

[v-qiuyu-msft]

Hi @frankmartin,

Is there any error throws out when you use MAX() function? Based on my test, MAX() function also works for string values. 

Best Regards,
Qiuyun Yu

[frankmartin]

@v-qiuyu-msft

[v-qiuyu-msft]

Hi @frankmartin,

Based on my test, the MAX() function works for string values. Please create a measure instead of calculated column then test again. 

I create PowerPivot table in Excel version below, you can download my Excel model to have a look. 

Best Regards,
Qiuyun Yu

[frankmartin]

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.