Complex DAX Calculation - Event Log Cycle Times for Process Mining

[elliottcarver]

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:

[JustJan]

Hi Eliot, 

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 .  

Not sure what the dymanics in step A and B would be, but for dynamic interaction I think you need measures. 

In a table it is possible to calculate durations, but that would be for a "fixed" set of values for step A and B.

Jan 

[JustJan]

Hi Elliot, 

Maybe my explanation wasn't too good either.

But you did proof my point. By hardcoding "   'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=0)" instead of 

"= 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.

So the only way to have a dynamic user interaction is via measures in a visual.  Unless you to use parameters that are applied during the data load. 

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 
https://community.powerbi.com/t5/Desktop/Pass-Filter-context-of-measure-into-the-calculated-column-i...

https://community.powerbi.com/t5/Desktop/Dynamic-Filtering-of-derived-DAX-table-based-on-the-filters...

Hope this helps a bit

jan 

[elliottcarver]

This is really helpful feedback Jan - thanks so much for your help 🙂

So, is there any way for me to measure the time between any Step A and any Step B using the same dataset?

Or should I make this report a template and create several copies of it with the same parameters?

There's so many AB Sets I'm interested in analyzing that I'm a little worried about overloading my BI Service with requests.

[JustJan]

Hi Elliot, 

It should be possible with the tables you use at the moment. 

The solution is something along the lines of your table formula. 

You will probably use a visual where data is grouped on 'Change Log Entry - Process Mining Entry'[Primary Key Field 2 Value]'

So the measure will use the 'Change Log Entry - Process Mining Entry'[Primary Key Field 2 Value]' as a filter. 

If you have some more representative data available I could have a look for you.

Jan 

[elliottcarver]

Sorry Jan - I don't think I did a good job explaining.

I have 3 tables:

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)

My report has a slicer with Step A, allowing the user to select a single value for Step A

There is a 2nd slicer for Step B

The report performs exactly as expected when I hardcode the values for Step A and Step B (0, 40 in the example below)

But I want the user to be able to select any Step ID from the Step A table, and see the results.

I attempted to do this in the table using = SELECTEDVALUE('Step A'[Step ID), but got an error.

I created this measure:

Step A Selection = SELECTEDVALUE('Step A'[Process Mining Step Index])

But this does not work either.

How can I get the table to refer to the single step that the user is choosing from Step A and Step B?

Table DAX

    summarize(
        'Change Log Entry - Process Mining Entry', 
        'Change Log Entry - Process Mining Entry'[Primary Key Field 2 Value],
        "Duration", 
    DATEDIFF(
        CALCULATE(
            
            Min('Change Log Entry - Process Mining Entry'[Date]),
            
            filter(
                'Change Log Entry - Process Mining Entry',
                'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=0)//= SELECTEDVALUE('Step A'[Step ID)
            
        ),
                
        CALCULATE(
        
               Min('Change Log Entry - Process Mining Entry'[Date]),
            
            filter(
                'Change Log Entry - Process Mining Entry',
                'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=40)/= SELECTEDVALUE('Step B'[Step ID)
            
        ),
            DAY)
            
            +1
            )

[JustJan]

Hi @elliottcarver ,

You could try something like this: 

Measure = 
countx(
    summarize(
        'Table', 
        'Table'[Case]
    ,    "Duration", 
    DATEDIFF(Min('Table'[Date]), Max('Table'[Date]), DAY)+1
), 
if ([Duration]<=[Parameter Value],1)
)

This is roughly the same as you pseudo code 🙂 [Parameter Value] is a What-If parameter for your DayTarget

jan

[elliottcarver]

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

This is my table function:

Table 1 =

summarize(

'Change Log Entry - Process Mining Entry',

'Change Log Entry - Process Mining Entry'[Primary Key Field 2 Value],

"Duration",

DATEDIFF(

CALCULATE(

        Min('Change Log Entry - Process Mining Entry'[Date]),         

        filter(

            'Change Log Entry - Process Mining Entry',

             'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=[Step A Selection])

    ),

    CALCULATE(

      Min('Change Log Entry - Process Mining Entry'[Date]),         

        filter(

            'Change Log Entry - Process Mining Entry',

             'Change Log Entry - Process Mining Entry'[Process Mining Step Index]=[Step B Selection])

    ),

     DAY)

        )

Step A Selection = SELECTEDVALUE('Step A'[Process Mining Step Index])