cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
smstrickland Helper I
Helper I

Help with SELECTED VALUE in a measure

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 Name1/3/20201/17/20201/31/20202/14/20202/28/20203/13/2020
Staff A012021
Staff B202000
Staff C202000
Staff D210201
Staff E012001
Staff F012221
Staff G002020
Staff H200020
Staff I010221
Staff J202221

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
smstrickland Helper I
Helper I

Re: Help with SELECTED VALUE in a measure

@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

7 REPLIES 7
Super User II
Super User II

Re: Help with SELECTED VALUE in a measure

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

Re: Help with SELECTED VALUE in a measure

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

TimesheetLineIdTimeByDayActual Work BillableCreatedDateLastChangedResourceNameResource NameTimeByDay_DayOfMonthTimeByDay_DayOfWeekTimesheetLineModifiedDateTimesheetLines.Timesheet Owner
fca7d4b2-c7ee-b76e-5701-00005188c2355/6/2019 0:0015/9/2019 12:29Hanson; BeverlyHanson; Beverly625/9/2019 12:29Hanson; Beverly
01e52a18-6a2d-4300-ad29-0001572f383a2/5/2019 0:000.52/7/2019 22:08Keilitz; SusanKeilitz; Susan532/7/2019 22:12Keilitz; Susan
961f45e5-4049-d7e9-5e12-0006446f2d543/15/2019 0:0013/15/2019 14:03Wright; CherylWright; Cheryl1563/15/2019 14:03Wright; Cheryl
4c2ffedd-641e-4452-b9c2-000804c0782b1/17/2019 0:000.51/18/2019 14:56Strickland. ShaunaStrickland; Shauna1751/18/2019 14:56Allred; Alice
e4c3cf36-b821-88ea-bc5b-0008bdbcee113/9/2018 0:001.55/9/2018 15:15Spacek; ShelleySpacek; Shelley965/9/2018 15:15Spacek; Shelley

 

And here is a sample of TimesheetLines:

TimesheetLineIdActual Work BillableCreatedDatePeriod End DatePeriod Start DateTimesheetLineStatusTimesheetLineStatusIdTimesheet OwnerTimesheet StatusTimesheetStatusId
dd2a2a57-035c-ea11-bf92-00155df4333cnull3/1/2020 21:582/28/2020 23:592/15/2020 0:00Approved1Bajandas; FelixIn Progress0
71ccd3d0-5365-ea11-bf93-00155df47c1633/13/2020 18:043/13/2020 23:592/29/2020 0:00Pending0Reier; DanielleIn Progress0
72ccd3d0-5365-ea11-bf93-00155df47c166.383/13/2020 18:043/13/2020 23:592/29/2020 0:00Pending0Reier; DanielleIn Progress0
5cc1794a-8dd9-e911-affa-00155df4ad1579/17/2019 20:555/24/2019 23:595/11/2019 0:00Pending0Kim; AnthonyIn Progress0
6ad27d29-a546-ea11-bf87-00155df4db2a42/3/2020 16:511/31/2020 23:591/18/2020 0:00Pending0Montalvo; EmilyIn Progress0
b1091b78-bdc3-dc3e-9bb6-001d6c56d1c7null4/1/2020 20:494/10/2020 23:593/28/2020 0:00Approved1Genthon; KathrynIn Progress0
7b7ecf0e-cf28-e5e6-3437-002a834bf112null10/14/2019 17:2310/11/2019 23:599/28/2019 0:00Approved1Griller; GordonIn Progress0
1919a587-cabd-e624-68dd-0039e0afcace8.53/31/2020 12:284/10/2020 23:593/28/2020 0:00Pending0Waters; NicoleIn Progress0
8997a7e2-8655-55ef-f4b3-004c6607c185null2/4/2020 17:591/31/2020 23:591/18/2020 0:00Approved1Ostheimer; AmyIn Progress0
e0a62e3d-0f05-0c2f-4a64-000030f0a846null4/30/2018 11:214/27/2018 23:594/14/2018 0:00Approved1Bryant; KristinaApproved3
fca7d4b2-c7ee-b76e-5701-00005188c23514/28/2019 21:045/10/2019 23:594/27/2019 0:00Approved1Hanson; BeverlyApproved3
224c6cf4-e6f9-0a92-18fe-0000ad2a5e0cnull9/30/2018 13:2010/12/2018 23:599/29/2018 0:00Approved1Waters; NicoleApproved3
25ae04b9-064d-7faf-4ddf-0000e3da2e7anull10/30/2019 13:4510/25/2019 23:5910/12/2019 0:00Approved1Ring; JacquieApproved3
747831a2-3be7-c0c4-707a-00013e8e6540null3/18/2019 21:033/15/2019 23:593/2/2019 0:00Approved1Burton; PamApproved3
01e52a18-6a2d-4300-ad29-0001572f383a32/5/2019 2:262/15/2019 23:592/2/2019 0:00Approved1Keilitz; SusanApproved3
3b033790-5673-bca4-c715-000197898d46null4/30/2019 18:495/10/2019 23:594/27/2019 0:00Approved1Klaversma; LauraApproved3
Super User II
Super User II

Re: Help with SELECTED VALUE in a measure

@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 Helper I
Helper I

Re: Help with SELECTED VALUE in a measure

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

Super User II
Super User II

Re: Help with SELECTED VALUE in a measure

@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 Helper I
Helper I

Re: Help with SELECTED VALUE in a measure

@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

Super User II
Super User II

Re: Help with SELECTED VALUE in a measure

@smstrickland 

I'm glad to hear it 🙂

Please, mark your last post as solution for future readers

Good luck!


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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors