Create a dynamic Leadtime filter, calculated only on the IDs which contains both selected values.

[PhilipL]

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: 

Ledtime =

VAR StartActivity = SELECTEDVALUE('Start Activity'[Activity])

VAR EndActivity = SELECTEDVALUE('End Activity'[Activity])

VAR StartTime = CALCULATE(AVERAGE('Table'[Timestamp]),'Table'[Activity] = StartActivity)

VAR EndTime = CALCULATE(AVERAGE('Table'[Timestamp]),'Table'[Activity] = EndActivity)

RETURN

DATEDIFF(StartTime, EndTime,HOUR)

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, 

[PhilipL]

@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

[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 you

[PhilipL]

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 .

[speedramps · Accepted solution]

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" relationships

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

[PhilipL]

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. 

[speedramps]

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.

 

 

[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 😊 🙏 

[PhilipL]

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! 

[speedramps]

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

[PhilipL]

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.