Forum Discussion
Create a dynamic Leadtime filter, calculated only on the IDs which contains both selected values.
Hi PhilipL
Click here to download the solution
Download PBIX
It works so please click the accept solution and thumbs up button.
How it works ....
Create "inactive" relationshipsCreate measures to get start and end time using the relationships
Start time = -- get time using start relationship CALCULATE( SELECTEDVALUE('Datatable'[Timestamp]), USERELATIONSHIP(StartActivities[Start Activity],'Datatable'[Activity]))End time = -- get time using end relationship CALCULATE( SELECTEDVALUE('Datatable'[Timestamp]), USERELATIONSHIP(EndActivities[End Activity],'Datatable'[Activity]))Create measure to calculate the lead time
LeadTime = -- get lead time unless the start or end is blank or the end is before the start SWITCH(TRUE(), ISBLANK([Start time]) || ISBLANK([End time]), BLANK(), [End time] < [Start time], BLANK(), [End time] - [Start time])Create a measure to get the average
Average LeadTime = -- get average as decical value, ignoring blank values VAR decimal_average = AVERAGEX( VALUES('Datatable'[Case]), [LeadTime] ) RETURN -- convert deccimal to hours and minutes FORMAT(decimal_average, "HH:MM")Then build your report
Thanks for the clear description of the problem with example data. I wish everyone did that!
Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos.One question per ticket please. If you need to change or extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.
Please now click the [accept as solution] and the thumbs up button. Thank you.
I ment no disrespect of course. All I am saying is that I lack better wording to verbaly explain my decires. I do not know how to attach any data into these posts, nor where I should upload a dummy dataset for anyone to download. If i knew that I would have provided that in the post of course.
Hopefully you will find this good enough:
Example Data:
Datatable:
| Case | Activity | Timestamp |
| Case1 | Create Order | 08:00:00 |
| Case1 | Pick Order | 09:00:00 |
| Case1 | Sell Order | 10:00:00 |
| Case2 | Create Order | 09:00:00 |
| Case2 | Pick Order | 10:00:00 |
| Case3 | Create Order | 08:00:00 |
| Case3 | Pick Order | 09:00:00 |
| Case3 | Package Order | 11:00:00 |
| Case4 | Create Order | 10:00:00 |
| Case4 | Sell Order | 11:00:00 |
StartActivitytable
| Activity |
| Create Order |
| Pick Order |
| Package Order |
| Sell Order |
EndActivityTable
| Activity |
| Create Order |
| Pick Order |
| Package Order |
| Sell Order |
Decired Outcome:
Example 1:
If I want to see the Leadtime between Created Order And Sell Order the measurement should generate an average datediff expressed in hours, between the activites Creared Order and Sell Order based on the Timestamps in the DataTable. Because Case 2 & Case 3 does not have the activity Sell Order, they should not be included in the calculation. Here we want the AverageLeadtime between Created Order and Sell Order from only Case 1 and Case 4.
This would be Averege of (08.00 and 10.00) being 09.00 to Average of (10.00 and 11.00) being 10.30. Thus in a Card we would like to see Average Leadtime: 1h 30 min.
Example 2:
If I want to see the Leadtime between Created Order And Pick Order. Because Case 4 does not have the activity Pick Order, it should not be included in the calculation. Here we want the AverageLeadtime between Created Order and Pick Order from only Case 1 Case 2 and Case 3.
This would be Averege of (08.00, 09.00, 08.00) being 08.20 to Average of (09.00, 10.00 09.00) being 09.20 Thus in a Card we would like to see Average Leadtime: 1h .
Super UserThanks PhilipL that is perfect and how all questions should be written in my opinion.
I am busy right now but hope to solve this before other solvers
No disrespect given or received.
Many thanks 😊 🙏
Thank you so much speedramps and thank you for providing me with good and clear comments on what you want to see in a post. The importent thing as you say is that you who are willing to help me, get material in the right format.
If you need any clarification please do not hessitate to reach out!