Process Mining in PowerBI, Duration of activities each case

[Anonymous]

Hello, I'm working with Process Mining Power Automate and their integration with PowerBI. Now, We want to create diferents dashboard in powerbi to make an analysis of the diferents process.
A Diagram that will be useful in the analysis is a Stacked bar chart for each case where it represent the duration for each activity order by the timestamp of the activity. (and then also will be usefull to create a gantt with also this information of diferents cases).
I am working with the predefined tables of ProcessMapMetrics (where exist the duration between the diferents activities , the edge duration) however I can not import or relation this table to get the timestamp or starttime of when this edge duration is done.

I share the tables relation in PBI with the Process Mining conexion and where to find the start time activity

The graph i want to get is sort of this picture

However the activity duration is not chronological order due to i dont have the timestamp

PD: I also prove with create a column in ViewEvents with the earlier timestamp of each case but is so computing demanding and it generate a cycle.

Thank you

[Anonymous]

@Sergii24 
Example of the tables
Process Map Metrics

This table has a

• CaseId (from the table 'cases') .
• Aggregation_ID (Mix between the activities in the case and the transition between the activities)  as well as Label
  
  • ->1 :  First transition of activity
  • 0: Activty 0 which name is candidateApplied and the attribute_Value_ID 
  • 0->2 : transition between the activity0 and the activity2 that last 46days

Table of ViewCases:

• CaseId : Id from the table Cases
• Label: the name of the activity (it would have a relation between the ViewCases and the Process Metrics) but it does not, because there are different activities for each cases. (1 case has several activities) 
• Start Time: the timestamp where the activity happen

As you can see, the activity candidateApplied an longlisted is in the same timestamp. Thats the transitions between ->1 and 1->0 which duration is 0 in the table of process Map Metrics
The next activities is onsiteShortlist, the diff time is 46 days (is already calculated in the process map Metric table) which is shown in the transition '0->2'. But It is not possible to map the activities.
A different way to do is just to calculate the datediff of the starttime in the table of ViewCases order by the caseId.
However I did not find the solution to create a column of the previous timestamp to calculate the timediff without the problem of cycle relationship. (Also the processing of all this data will be huge)

[Sergii24]

If I understand you well, you need to calculate time between activities within the same CaseId. Is it correct?

You have timestamp for each of them in "ViewCases" table, but don't want to calculate it there as the table is too big. In addition, you already have the necessary calcualtion result in table "Process Map Metrics" (that contains the same CaseId).

So what is a problem to connect two tables? Don't you the order of acitvities (i.e. that "candidateApplied" is happeinng before "longListed")? Or is it the structure of "Process Map Metrics" and the fact that there are rows with:

• 0 duration and activity name
• real duration and activity movement written as a string (like 0->2)

[Anonymous]

Hi @Sergii24 , I will provide the much context to know my issue.
The main problem is that using the Power Automate Process Mining tool of microsoft (which is so helpful for analysing the process). It creates a predifined tables data structure of the logs that we have import in the process. 
 This is the link of microsoft Use the optimized data structure in a Power BI report (preview) - Power Automate | Microsoft Learn
In this case, we have the data in PowerBI, but is not in a proper structure to create some graphs. And which is a big issue is that you can not modified the relationship between the predefines tables. 

[Sergii24]

Hi @Anonymous, thanks for the background of your project! It's always nice to have 🙂

However I'm probably missing something to understand your issue. What your question is about?

• Are you missing some data that you'd like to visualize? Well, if this is a case, I don't think PowerBI community forum is the right place to ask (would be better to refer to Power Automate experts - Microsoft Power Automate Community - Power Platform Community).
• Do you have data but have issue to plot the data? Then please provide a sample of your data, details on what exactly you struggle with and the desired output.