Forum Discussion
Create a dynamic Leadtime filter, calculated only on the IDs which contains both selected values.
Hello,
I am trying to build a DAX measurement to calculate average DATEDIFF where you can through usage of SELECTEDVALUE decide decide which timestamps to calculate the DATEDIFF on.
In a simple world this would be enough:
However, my strugles are what is comming next.
Let us say I have 10 unique Cases all having the StartActivity, and that I have 20 Cases all having the EndActivity. However, it is only 8 Cases that have both the Start Activity and the EndActivity. Meaning:
CALCUATE(COUNTROWS(Table), FILTER(Table, OR(Table[Activity] = StartActivity, Table[Activity]= EndActivity))) = 2
I have spent hours trying to fingure out the correct DAX syntax for being able to generate:
VAR StartTime = CALCULATE(AVERAGE('Table'[Timestamp]),'Table'[Activity] = StartActivity) but where it is filtering the table to only include the Cases where we have both the StartActity and the EndActivity.
I tried to make a conceptual picture below.
Here is a actuall picture of how the Table looks
Please help me update my DAX code so that it when I do Average on a specific Column, it filters the table both on the Column information, but also that there must exist a row with the second activity,
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.
10 Replies
speedrampsSuper User
We want to help you but your description is too vaugue. Please write it again clearly.
Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That is a bit crazy. 😀Please just give a simple non technical functional decscription of what you want, then let us suggest the DAX. Thank you.
Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output to diagram with a clear description of the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Try keep it simple and just ask one question per post.
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Vaugue descriptions can waste your time and ourtime.
Look foward to helping you when the above information is forthcoming
Hello,
I do not know how to make myself more clear than I have.
I want to create a DAX measurement that generates the Average Leadtime between two different activities, but it must at the same time filter the data so that we only look at cases that have both activites.
There are a total of 40 different activites and you want to be able to easily "Pick" which activity to define as start and end.
Case + activity is a row in the table and we cannot have one column per activity duo to other limitations.
- speedramps
We are unpaid volunteers. You are getting free expert help.
You will gain respect and quicker better answers the more care and effort you put into questions rather than say "I do not know how to make myself more clear than I have. "
Provide example input data as table text (not screen prints) so we can import the data to build a solution for you.
I wont input your test data for you.
Also provide a diagram of the desired solution clearly showing the logic and calculations.
We want to help you but you need to help us help youI 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
StartActivitytableActivity Create Order Pick Order Package Order Sell Order
EndActivityTableActivity 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 .- speedramps
Thanks 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 😊 🙏
speedramps Sorry for writing here, but I have a bug with messages so I cannot send more messages. Microsoft confirms that it is a bug, but until that is solved I will write here.
Thank so again.
To simply I am trying to build a Impact measurement to see which Case that has the most negative impact on Leadtime. For this analysis to be useful I need to look at it from a "attribute/value" perspective.
Here we have one table with all the Timestamps, the table you have already built the measurement for, and then there is an Attribute table with all the attributes related to the Case. The Timestamp table we can have the following :
Case
Activity
Timestamp
Case1
Create Order
08:00
Case1
Pick Order
09:00
Case1
Package Order
10:00
Case2
Create Order
09:00
Case2
Pick Order
09:30
Case2
Ship Order
14:00
Case3
Order Created
09:00
Case3
Pick Order
09.10
Case3
Package Order
14:00
And then in the Attribute Table we can information about the Order:
Case
Attribute
Value
Case1
Order Created By
EDI
Case1
Customer
Builder Co.
Case1
Purchase method
Web
Case2
Order Created By
Bob Smith
Case2
Customer
Builder Co.
Case2
Purchase method
Cash
Case3
Order Created By
Bob Smith
Case3
Customer
Distributor Co.
Case3
Purchase method
Web
The ultimate goal is to have a Matrix Visual in Power BI, where I on the Rows have the Attribute followed by Values. On the Value section of the Matrix I then want to add the Impact measurement which is taking:
(1) Average Leadtime between the selected Actvities of all the Cases that have the specific Attribute+Value, and then see how many cases in of that specific Attribute+Value have the selected Activites. With the Average Leadtime per Attribute+Value together with NrOfCases that has the selected Activities.
With some Paint drawing added, it should look like the following:
And then the Impact measurement should look somthing like this. (the impact measurenet used in the picture below is inaccurate, so just for visual understanding):
Worth adding: The Order of the Activities is not always in the Timestamp table is not always occuring in the same order. This is why I wanted to have 2 Activity tables for Start and End activity originally. This might not be needed, but there is not "set chain of events" unfortunately. However, the logic presented about I will use in other parts of the report where I do have a set chain of events, so I thank you for this smart solution as well.Best Regards,
Philip
