Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am attempting to create a measure that will show if a resource's timesheet has been created, is in progress, has been rejected, or has been approved for a particular time period. Here is the measure as written so far:
SWITCH(
TRUE(),
SUM(TimesheetLineActualDataSet[Actual Work Billable])=0, 0,
SELECTEDVALUE(TimesheetLines[TimesheetStatusId])<3 && SUM(TimesheetLineActualDataSet[Actual Work Billable])>0, 1,
SELECTEDVALUE(TimesheetLines[TimesheetStatusId])=3 && SUM(TimesheetLineActualDataSet[Actual Work Billable])>0, 2,
SELECTEDVALUE(TimesheetLines[TimesheetStatusId])>3 && SUM(TimesheetLineActualDataSet[Actual Work Billable])>0, 0
)
where [Actual Work Billable]=0 tells me that a timesheet has not been created while [Actual Work Billable]>0 says that a timesheet has been created. [TimesheetStatusID]<3 indicates "in progress"; =3 indicates "approved"; and >3 indicates "rejected."
With this measure, my resulting table looks like this:
Resource Name | 1/3/2020 | 1/17/2020 | 1/31/2020 | 2/14/2020 | 2/28/2020 | 3/13/2020 |
Staff A | 0 | 1 | 2 | 0 | 2 | 1 |
Staff B | 2 | 0 | 2 | 0 | 0 | 0 |
Staff C | 2 | 0 | 2 | 0 | 0 | 0 |
Staff D | 2 | 1 | 0 | 2 | 0 | 1 |
Staff E | 0 | 1 | 2 | 0 | 0 | 1 |
Staff F | 0 | 1 | 2 | 2 | 2 | 1 |
Staff G | 0 | 0 | 2 | 0 | 2 | 0 |
Staff H | 2 | 0 | 0 | 0 | 2 | 0 |
Staff I | 0 | 1 | 0 | 2 | 2 | 1 |
Staff J | 2 | 0 | 2 | 2 | 2 | 1 |
It seems to pick up the no timesheet created condition as it should, but does not correctly return the status of each timesheet that has been created. Instead, it appears to take the status of the first timesheet that it encounters and returns that status for all the timesheets in that pay period.
I appreciate any help that can be provided in how to fix this. Thank you. --Shauna
Solved! Go to Solution.
@az38 Thank you for attempting to help me. I realized that the problem was being caused by a missing relationship between the two tables and was able to fix the problem.
Hi Shauna @smstrickland
demonstrate please how your data looks like in origin data source
and for better memory consuming it would be good to use repetable statements in variables like
Measure =
var _sumActualWorkBillable = SUM(TimesheetLineActualDataSet[Actual Work Billable])
var _selectedTimesheetStatusId = SELECTEDVALUE(TimesheetLines[TimesheetStatusId])
RETURN
SWITCH(
TRUE(),
_sumActual Work Billable = 0, 0,
_selectedTimesheetStatusId < 3 && _sumActualWorkBillable > 0, 1,
_selectedTimesheetStatusId = 3 && _sumActualWorkBillable > 0, 2,
_selectedTimesheetStatusId > 3 && _sumActualWorkBillable > 0, 0
)
@az38 The two data tables involved in this measure are a small subset of the tables that I have in the model. The other two data tables that are included in this specific visual are titled Resources and Timesheet Periods. Here is the relationship of these 4 data tables:
Resources has a 1-to-Many active relationship with TimesheetLineActualDataSet via 'Resource Name' to 'TimesheetLines.Timesheet Owner,'
Timesheet Periods has a 1-to-Many relationship with Timesheets via 'Period ID,'
Timesheets has a 1-to-Many relationship with TimesheetLines via 'Timesheet ID,' and
TimesheetLines has a 1-to-Many relationship with TimesheetLineActualDataSet via 'TimesheetLine ID.'
Here is a sample of TimesheetLineActualDataSet:
TimesheetLineId | TimeByDay | Actual Work Billable | CreatedDate | LastChangedResourceName | Resource Name | TimeByDay_DayOfMonth | TimeByDay_DayOfWeek | TimesheetLineModifiedDate | TimesheetLines.Timesheet Owner |
fca7d4b2-c7ee-b76e-5701-00005188c235 | 5/6/2019 0:00 | 1 | 5/9/2019 12:29 | Hanson; Beverly | Hanson; Beverly | 6 | 2 | 5/9/2019 12:29 | Hanson; Beverly |
01e52a18-6a2d-4300-ad29-0001572f383a | 2/5/2019 0:00 | 0.5 | 2/7/2019 22:08 | Keilitz; Susan | Keilitz; Susan | 5 | 3 | 2/7/2019 22:12 | Keilitz; Susan |
961f45e5-4049-d7e9-5e12-0006446f2d54 | 3/15/2019 0:00 | 1 | 3/15/2019 14:03 | Wright; Cheryl | Wright; Cheryl | 15 | 6 | 3/15/2019 14:03 | Wright; Cheryl |
4c2ffedd-641e-4452-b9c2-000804c0782b | 1/17/2019 0:00 | 0.5 | 1/18/2019 14:56 | Strickland. Shauna | Strickland; Shauna | 17 | 5 | 1/18/2019 14:56 | Allred; Alice |
e4c3cf36-b821-88ea-bc5b-0008bdbcee11 | 3/9/2018 0:00 | 1.5 | 5/9/2018 15:15 | Spacek; Shelley | Spacek; Shelley | 9 | 6 | 5/9/2018 15:15 | Spacek; Shelley |
And here is a sample of TimesheetLines:
TimesheetLineId | Actual Work Billable | CreatedDate | Period End Date | Period Start Date | TimesheetLineStatus | TimesheetLineStatusId | Timesheet Owner | Timesheet Status | TimesheetStatusId |
dd2a2a57-035c-ea11-bf92-00155df4333c | null | 3/1/2020 21:58 | 2/28/2020 23:59 | 2/15/2020 0:00 | Approved | 1 | Bajandas; Felix | In Progress | 0 |
71ccd3d0-5365-ea11-bf93-00155df47c16 | 3 | 3/13/2020 18:04 | 3/13/2020 23:59 | 2/29/2020 0:00 | Pending | 0 | Reier; Danielle | In Progress | 0 |
72ccd3d0-5365-ea11-bf93-00155df47c16 | 6.38 | 3/13/2020 18:04 | 3/13/2020 23:59 | 2/29/2020 0:00 | Pending | 0 | Reier; Danielle | In Progress | 0 |
5cc1794a-8dd9-e911-affa-00155df4ad15 | 7 | 9/17/2019 20:55 | 5/24/2019 23:59 | 5/11/2019 0:00 | Pending | 0 | Kim; Anthony | In Progress | 0 |
6ad27d29-a546-ea11-bf87-00155df4db2a | 4 | 2/3/2020 16:51 | 1/31/2020 23:59 | 1/18/2020 0:00 | Pending | 0 | Montalvo; Emily | In Progress | 0 |
b1091b78-bdc3-dc3e-9bb6-001d6c56d1c7 | null | 4/1/2020 20:49 | 4/10/2020 23:59 | 3/28/2020 0:00 | Approved | 1 | Genthon; Kathryn | In Progress | 0 |
7b7ecf0e-cf28-e5e6-3437-002a834bf112 | null | 10/14/2019 17:23 | 10/11/2019 23:59 | 9/28/2019 0:00 | Approved | 1 | Griller; Gordon | In Progress | 0 |
1919a587-cabd-e624-68dd-0039e0afcace | 8.5 | 3/31/2020 12:28 | 4/10/2020 23:59 | 3/28/2020 0:00 | Pending | 0 | Waters; Nicole | In Progress | 0 |
8997a7e2-8655-55ef-f4b3-004c6607c185 | null | 2/4/2020 17:59 | 1/31/2020 23:59 | 1/18/2020 0:00 | Approved | 1 | Ostheimer; Amy | In Progress | 0 |
e0a62e3d-0f05-0c2f-4a64-000030f0a846 | null | 4/30/2018 11:21 | 4/27/2018 23:59 | 4/14/2018 0:00 | Approved | 1 | Bryant; Kristina | Approved | 3 |
fca7d4b2-c7ee-b76e-5701-00005188c235 | 1 | 4/28/2019 21:04 | 5/10/2019 23:59 | 4/27/2019 0:00 | Approved | 1 | Hanson; Beverly | Approved | 3 |
224c6cf4-e6f9-0a92-18fe-0000ad2a5e0c | null | 9/30/2018 13:20 | 10/12/2018 23:59 | 9/29/2018 0:00 | Approved | 1 | Waters; Nicole | Approved | 3 |
25ae04b9-064d-7faf-4ddf-0000e3da2e7a | null | 10/30/2019 13:45 | 10/25/2019 23:59 | 10/12/2019 0:00 | Approved | 1 | Ring; Jacquie | Approved | 3 |
747831a2-3be7-c0c4-707a-00013e8e6540 | null | 3/18/2019 21:03 | 3/15/2019 23:59 | 3/2/2019 0:00 | Approved | 1 | Burton; Pam | Approved | 3 |
01e52a18-6a2d-4300-ad29-0001572f383a | 3 | 2/5/2019 2:26 | 2/15/2019 23:59 | 2/2/2019 0:00 | Approved | 1 | Keilitz; Susan | Approved | 3 |
3b033790-5673-bca4-c715-000197898d46 | null | 4/30/2019 18:49 | 5/10/2019 23:59 | 4/27/2019 0:00 | Approved | 1 | Klaversma; Laura | Approved | 3 |
whats your desired output for this part of daatset and what Date you use as a column in your matrix from the first post?
@az38 I'm using TimesheetPeriods[End Date] as the date field, and the output should be the following:
For each person, for each pay period there should be a
0 if the person either did not submit a timesheet or if they did submit a timesheet and it has a status of 4 or 5;
1 if the person submitted a timesheet and the timesheet status is 0, 1 or 2; or
2 if the person submitted a timesheet and the timesheet status is 3.
your data is very complicated and there is not enough data example to recreate your project.
please, remove all sensitive data from your pbix-file and upload it to any cloud file-sharing service
@az38 Thank you for attempting to help me. I realized that the problem was being caused by a missing relationship between the two tables and was able to fix the problem.
I'm glad to hear it 🙂
Please, mark your last post as solution for future readers
Good luck!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |