none
How to join multiple SharePoint lists in Power Pivot

    Question

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

    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?

    Wednesday, November 08, 2017 10:40 AM

All replies

  • Anyone?
    Wednesday, November 08, 2017 5:54 PM
  • Hi Frank Martin Consultant,

    Thanks for your question.

    >>>Problem is I cannot join LeaveTable with LeaveAvailableTable because they both contain multiple values and Excel doesn't allow it.
    According to your description, it is not necessary to  join LeaveTable with LeaveAvailableTable, as they are already connected by EmployeeID.See below image:

    >>>Is there a way to make this report?
    You can add a calculated column called [TotalLeavesTaken] in table LeavesAvailable as below:

    sumx(filter('Leaves', 'Leaves'[EmployeeID] = LeavesAvailable[EmployeeID] && 
    'Leaves'[LeaveType] =LeavesAvailable[LeaveType]),
    'Leaves'[NoOfLeavesTaken])

    Then create 2 sum measure as below:

    SumofTotalLeavesAvailable:=SUM([TotalLeavesAvailable]) SumofTotalLeavesTaken:=SUM([TotalLeavesTaken])

    After that, you can just create a Power Pivot report by dragging EmployeeID,EmployeeName in Employee table and LeaveTypeTitle in LeaveAvailableTable table to ROWS, and dragging SumofTotalLeavesAvailable and SumofTotalLeavesTaken to VALUES.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Thursday, November 09, 2017 7:25 AM
  • Thanks. This really cleared some points and my problem is almost solved. Just one thing. There is a fourth table which I didn't mention in the original post because I thought it would make it complex. But now my requirement is such that I need it.

    Basically, I want to display one last column at the end which will show AvailableBalanace i.e. LeavesAvailable - NoOfLeavesTaken

    Problem is Leaves tables only contain LeaveTypeTitle field which is like Annual, Casual etc. and not LeaveTypeCode field which is like 01, 02, 03.

    Similarly, LeavesAvailable table only contains leave codes for e.g. 01, 02 but not their titles.

    There is a 4th table which contains both LeaveCode and LeaveTitles so it becomes something like:

    01, Annual
    02, Casual

    So I have joined this new table with Leaves and LeaveAvailable tables with their respective code and title columns which is working fine.

    Now I went on to add another column in LeaveAvailable table which will show AvailableBalance by deducting SumOfLeavesAvailable with NoOfLeavesTaken BUT it seems like FILTER function in Excel can only be used to join two tables and not three.

    So how can I add calculated column to LeavesAvaiable table?

    Thursday, November 09, 2017 10:35 AM
  • I tried to add a new column which will show "LeavesTaken" like you have shown in your example but this time I am using join from 4th table. It seems the formula I am using need some tweaking or joining with another table but I don't know how to do that.



    Thursday, November 09, 2017 10:58 AM
  • Hi Frank Martin Consultant,

    Thanks for your response.

    Since your original issue has been resolved, please mark the corresponding replies as answers. For the new issues with a fourth table data model, I would recommend you to post a new question, thanks for your understanding and support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, November 10, 2017 1:52 AM