Forum Discussion
Create a dynamic Leadtime filter, calculated only on the IDs which contains both selected values.
- 2 years ago
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 am humble by your solution speedramps . Such a smart approach. You did not just solve my problem, but you taught me a new way of thinking in DAX.
Thank you for taking your time and helping me with this. I can confirm that the solution is working just as I wanted and the result is just such a important input to my reports.
Thanks PhilipL
I am an unpaid volunteer and derive pleasure from teaching and explaining techniques and best pratices with clear examples and annotations, rather than just give quick vague answers.
Quote @speedramps in future questions and I will receive a notification and try help again.