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

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