Forum Discussion
Complex DAX Calculation - Event Log Cycle Times for Process Mining
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
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
)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-in-DAX/td-p/422553Hope this helps a bit
jan
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.
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
