Solved: Re: Help with SELECTED VALUE in a measure

smstrickland · ‎04-03-2020

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

smstrickland · ‎04-06-2020

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

View solution in original post

az38 · ‎04-03-2020

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
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

smstrickland · ‎04-03-2020

@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

az38 · ‎04-04-2020

@smstrickland

whats your desired output for this part of daatset and what Date you use as a column in your matrix from the first post?

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

smstrickland · ‎04-06-2020

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

az38 · ‎04-06-2020

@smstrickland

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

smstrickland · ‎04-06-2020

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