Complex DAX Calculation - Event Log Cycle Times for Process Mining

Question

Trying to create a model for cycle-time reporting that allows a report-user to know how often the time between any two steps met a variable target.

I want report users to input the following variables using slicers:

- Step A – single selection from a list of steps

- Step B – same as above

- Day Target – a range of values from -1000 to 1000 (this table can be added using M)

- Date Range – slicer using dates

The idea is that Step A and Step B are variables. The user can choose any Step ID as Step A, and any other Step ID as Step B. The measure will count the number of cases where the time between the first instance of Step A and the first instance of Step B is less than or equal to the Day Target variable.

DAX Measure Pseudocode = Count of Cases where [ Min(Step B.Date) – Min(Step A.Date) ] <= [Day Target]

Sample Data

Entry No	User ID	Case	Step ID	Date
1	STRAD\JHETFIELD	DO000001	0	January 7, 2020
2	STRAD\JHETFIELD	DO000001	1	January 7, 2020
3	STRAD\JHETFIELD	DO000002	1	January 8, 2020
4	STRAD\JHETFIELD	DO000002	0	January 7, 2020
5	STRAD\JHETFIELD	DO000003	0	January 7, 2020
6	STRAD\JHETFIELD	DO000003	1	January 9, 2020
7	STRAD\JHETFIELD	DO000004	1	January 7, 2020
8	STRAD\JHETFIELD	DO000004	0	January 15, 2020

Using this data as an example:

Step A = Step ID=1

Step B = Step ID=2

Day Target = 2

Measure Result = 2

(two of the cases have a time between Step 0 and 1 of less than or equal to 2 days)

Note that I'm not interested in doing this in M. The report-user needs to be able to change variables on the report to choose Step A and Step B, and adjust the Day Target dynamically.

Example of the Reporting Interface I'm looking for:

anonymous · Answer

Hi elliottcarver ,&nbsp;You could try something like this:&nbsp;&nbsp;Measure = countx(&nbsp;&nbsp;&nbsp; summarize(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Table', &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Table'[Case]&nbsp;&nbsp;&nbsp; ,&nbsp;&nbsp;&nbsp; "Duration", &nbsp;&nbsp;&nbsp; DATEDIFF(Min('Table'[Date]), Max('Table'[Date]), DAY)+1), if ([Duration]&lt;=[Parameter Value],1))This is roughly the same as you pseudo code 🙂 [Parameter Value] is a What-If parameter for your DayTarget&nbsp;jan

elliottcarver · Answer

Thanks for your reply JustJan!! I'm making headway on this initiative, but having trouble coming up with the best way to refer to my dynamic Step A and Step B selected variable. Currenly [Duration] evaluates to 0 for every record&nbsp;&nbsp;&nbsp;&nbsp;This is my table function:&nbsp;Table 1 = summarize('Change Log Entry - Process Mining Entry', 'Change Log Entry - Process Mining Entry'[Primary Key Field 2 Value],"Duration", DATEDIFF(&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Min('Change Log Entry - Process Mining Entry'[Date]), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;filter(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry',&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=[Step A Selection])&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Min('Change Log Entry - Process Mining Entry'[Date]), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;filter(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry',&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=[Step B Selection])&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp; DAY)&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;Step A Selection = SELECTEDVALUE('Step A'[Process Mining Step Index])

anonymous · Answer

Hi Eliot,&nbsp;&nbsp;I see that you want to create a table. But at the moment that you create a table there are never filters active, so there is no filter active on Step A when you create the table .&nbsp;&nbsp;&nbsp;Not sure what the dymanics in step A and B would be, but for dynamic interaction I think you need measures.&nbsp;In a table it is possible to calculate durations, but that would be for a "fixed" set of values for step A and B.&nbsp;Jan&nbsp;&nbsp;

elliottcarver · Answer

Sorry Jan - I don't think I did a good job explaining.&nbsp;I have 3 tables:&nbsp;1. Change Log Entry (event logs of Steps)2. Step A (types of Steps)3. Step B (identical to Step A, loaded a second time)&nbsp;My report has a slicer with Step A, allowing the user to select a single value for Step AThere is a 2nd slicer for Step B&nbsp;The report performs exactly as expected when I hardcode the values for Step A and Step B (0, 40 in the example below)&nbsp;But I want the user to be able to select any Step ID from the Step A table, and see the results.&nbsp;I attempted to do this in the table using&nbsp;= SELECTEDVALUE('Step A'[Step ID), but got an error.I created this measure:Step A Selection =&nbsp;SELECTEDVALUE('Step A'[Process Mining Step Index])&nbsp;But this does not work either.&nbsp;How can I get the table to refer to the single step that the user is choosing from Step A and Step B?&nbsp;Table DAX&nbsp; &nbsp;&nbsp;summarize(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry'[Primary Key Field 2 Value],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Duration",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATEDIFF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Min('Change Log Entry - Process Mining Entry'[Date]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;filter(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=0)//= SELECTEDVALUE('Step A'[Step ID)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Min('Change Log Entry - Process Mining Entry'[Date]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;filter(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=40)/= SELECTEDVALUE('Step B'[Step ID)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DAY)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;+1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)

anonymous · Answer

Hi Elliot,&nbsp;&nbsp;Maybe my explanation wasn't too good either.&nbsp;But you did proof my point. By hardcoding "&nbsp;&nbsp;&nbsp;'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=0)" instead of&nbsp;"= SELECTEDVALUE('Step A'[Step ID)" you influenced the operation at the time the table is created and getting the desired result.Filters (slicers or otherwise) are not taken into account when creating a table. By the way, the same applies to a calculated column.&nbsp;So the only way to have a dynamic user interaction is via measures in a visual.&nbsp; Unless you to use parameters that are applied during the data load.&nbsp;&nbsp;I tried to find an article on this behaviour, but I could not find it immediately. But there are some topics on the forum where the same 'problem' is described&nbsp;https://community.powerbi.com/t5/Desktop/Pass-Filter-context-of-measure-into-the-calculated-column-in-DAX/td-p/422553https://community.powerbi.com/t5/Desktop/Dynamic-Filtering-of-derived-DAX-table-based-on-the-filters/td-p/222856&nbsp;Hope this helps a bit&nbsp;jan&nbsp;&nbsp;

Forum Discussion

Complex DAX Calculation - Event Log Cycle Times for Process Mining

7 Replies

Related content

PySpark Notebook to process complex JSON

COMPLEX WORKDAYS CALCULATION

Complex duplication removal process

Complex DAX Calculations with Missing Dates

Calculation Group and Complex Measures