Forum Discussion
on time delivery
Hi all,
Newby here, please go easy.
I need to be able to visualize some performance measurements based on variables spread over 3 tables.
My table structure:
Table1: Incidents
| IncidentID | IncidentNr |
| 11 | A1202-2345 |
| 12 | A1202-2360 |
| 13 | A1202-2370 |
Table2: Incidentdurationblocks
| DurationBlockID | DurationNameID | IncidentID | Duration |
| 1 | 1 | 11 | 90 |
| 2 | 1 | 11 | 60 |
| 3 | 2 | 13 | 30 |
Table3: DurationNames
| DurationNameID | Name | Target |
| 1 | Registered | 60 |
| 2 | In Process | 90 |
| 3 | On Hold | 30 |
I need to generate 2 visualizations:
1 were i show a list of incidents where, per incident i can show "on time" or "late" when comparing the duration with target.
1 where i show a percentage on time for tickets comparing duration with target, with the option to filter per DurationName.
I probably need to do this using measures, but as a historical
Hi broeren79
Assuming that your model looks like this
you can create a calc column to get you on time and late values
Status = if([Duration] > RELATED(DurationNames[Target]), "Late", "On Time")and a measure for your % on time
% On Time = DIVIDE(CALCULATE(COUNTROWS('Incidentdurationblocks'), filter('Incidentdurationblocks','Incidentdurationblocks'[Status] = "On Time")), COUNTROWS('Incidentdurationblocks'))I hope this helps,
Richard
6 Replies
- Greg_Deckler
Community Champion
broeren79 - I cover On Time in Full in Chapter 9 of DAX Cookbook. You can download the PBIX here: https://github.com/gdeckler/DAXCookbook
thanks Greg_Deckler , i'll be sure to sure to check out the cookbook.
richbenmintzResident Rockstar
Hi broeren79
Assuming that your model looks like this
you can create a calc column to get you on time and late values
Status = if([Duration] > RELATED(DurationNames[Target]), "Late", "On Time")and a measure for your % on time
% On Time = DIVIDE(CALCULATE(COUNTROWS('Incidentdurationblocks'), filter('Incidentdurationblocks','Incidentdurationblocks'[Status] = "On Time")), COUNTROWS('Incidentdurationblocks'))I hope this helps,
Richard
Thanks richbenmintz , It was the "related" function i overlooked.
For the on time delivery percentage I multiplied it by 100 to get the percentage, but that's a minor thing.
- edhans
Hi broeren79 ,
See if this helps. I am not sure I fully understood your desired output, so I may need to redo the formulas.
My PBIX is here so you can see the measures I used to calculate the Time Status and Percent On Time columns. Also note in Power Query I merged your last two tables together. No point in them being separate in the model that I can see.
