Forum Discussion

PhilipL's avatar
PhilipL
Frequent Visitor
2 years ago
Solved

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 simp...
Need Help
  • speedramps's avatar
    speedramps
    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" 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's avatar
PhilipL
Frequent Visitor
2 years ago

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:

CaseActivityTimestamp
Case1Create Order08:00:00
Case1Pick Order09:00:00
Case1Sell Order10:00:00
Case2Create Order09:00:00
Case2Pick Order10:00:00
Case3Create Order08:00:00
Case3Pick Order09:00:00
Case3Package Order11:00:00
Case4Create Order10:00:00
Case4Sell Order11: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's avatar
speedramps
Icon for Super User rankSuper User
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" 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's avatar
    PhilipL
    Frequent Visitor
    2 years ago

    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's avatar
      speedramps
      Icon for Super User rankSuper User
      2 years ago

      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.