Problem Overview : I have run data for jobs which run daily. Each run has steps which are incremental and each step has duration logged against it. There are historical runs for each run which are mapped to a ParentAppId. The errors against each run is logged too. The historical runs mapped to a ParentAppId are mapped to a run and are used for calculating the duration variance for a run. I need to get the top 10 issues across all these tables considering any duration variance along with the errors and list out them. The top 10 issues should be looked by considering if there are more errors versus the variance.
For Ex:
1. If an error E2 has a count of 10 for a run and there is no variance then E2 is the top variance.
2. If there is a duration variation of -30 say for StepID 1 and only 1 error E1 with count of 1 , then StepID 1 with variance of -30 is the top variance.
The different tables per below
1. RunData
| RunID | StepID | Duration |
| 1 | 1 | 100 |
| 1 | 2 | 200 |
| 2 | 1 | 120 |
| 2 | 2 | 300 |
| 3 | 1 | 50 |
| 3 | 2 | 100 |
2. ParentAppRunMapper
3. Error
| RunID | Error | Count |
| 1 | E1 | 10 |
| 1 | E2 | 1 |
| 2 | E3 | 2 |
| 3 | E4 | 3 |
4. HistoricalRun
| ParentAppID | StepID | Duration |
| A | 1 | 120 |
| A | 2 | 180 |
| B | 1 | 150 |
| B | 2 | 200 |
5. StepDetails
6. TopIssues
| IssueID | RunID | VarianceName | Variance | IssueType |
| 1 | 1 | E1 | 10 | Error |
| 2 | 1 | S1 | -20 | Variance |
| 3 | 2 | S2 | -30 | Variance |
| 4 | 2 | E2 | 3 | Error |
| 5 | 3 | E3 | 3 | Variance |
The first 5 tables will be used to derive the 6th table (TopIssues). Once the calculation is done using the DAX the output will be per the 6th table. The following calculations should be done :
- Duration variance between a run (RunData table) & its historical run data (HistoricalRun table)
- List out the top 10 issues (across the duration variance per above & error data for a run) to a new table (TopIssues table) for all runs .
I will use this to be listed out in the Smart Narrative visual per below:
Top issues for this run (RunID : 1) are per below:
- E1 error has occured 10 times
- S1 has a variance of 20 seconds more than the historical run
Question :
1. How can i write a DAX measure to calculate the duration variance across multiple tables
2. How can i write a DAX measure to arrive at the top 10 issues across the duration variance & errors by considering the weightage of the errors versus the duration variance.
Please find here the pbix file i have used.
