Forum Discussion

elliottcarver's avatar
elliottcarver
Frequent Visitor
6 years ago

Complex DAX Calculation - Event Log Cycle Times for Process Mining

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 vari...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 

 

elliottcarver's avatar
elliottcarver
Frequent Visitor
6 years ago

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
            )