The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
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
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.
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
MAX function is not working because EmployeeID type is string and not numeric.
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
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |