Forum Discussion
Maintenance Tracking - What am I missing?
I was hoping someone might be able to help me start on this. I’m trying to create a few dashboards for our Operational teams to track maintenance being done.
We have a master list of all maintenance checks required to be done at each site, based on the asset. Not each site has every asset. I’ve done a combine in order to have a unique identifier for every check being done (Mtc Check) (examples below…)
Maintenance Check Master List | ||
Location | Asset | Mtc Check |
Site 1 | Asset 1 | Site 1 Asset 1 |
Asset 2 | Site 1 Asset 2 | |
Asset 3 | Site 1 Asset 3 | |
Asset 4 | Site 1 Asset 4 | |
Site 2 | Asset 1 | Site 2 Asset 1 |
Asset 2 | Site 2 Asset 2 | |
Asset 3 | Site 2 Asset 3 | |
Asset 4 | Site 2 Asset 4 | |
Asset 5 | Site 2 Asset 5 | |
Site 3 | Asset 1 | Site 3 Asset 1 |
Asset 2 | Site 3 Asset 2 | |
Asset 3 | Site 3 Asset 3 | |
Count: 31,000 |
We also have a generated report showing what checks have been completed based on the Mtc Check unique identifier.
Completed Maintenance Checks | |||
Mtc Check | Date | Technician | Issue |
Site 1 Asset 1 | Warning | ||
Site 1 Asset 2 | |||
Site 1 Asset 3 | |||
Site 1 Asset 4 | Critical | ||
Site 2 Asset 1 | |||
Site 2 Asset 2 | |||
Site 2 Asset 3 | |||
Site 2 Asset 4 | Warning | ||
Site 1 Asset 1 | Critical | ||
Site 1 Asset 2 | |||
Site 1 Asset 3 | Warning | ||
Site 1 Asset 4 | |||
Count: 23,000 |
We presently have around 250 sites with a total of 31,000 Mtc Checks. Presently I’m only showing around 23,000 Completed Maintenance Checks.
A few things to note:
- Obviously, not every Mtc Check has been completed
- Some checks have been competed multiple times
- Some checks have been missed at some sites
- The Technician can flag a site as having a low impact (warning) or high impact (critical) issue, which gets flagged and reviewed, but most sites don’t have any issues.
End Goal: I’d like a count of the number of times each Mtc Check has been completed but, and more importantly, which Mtc Checks have never been completed.
I’ve already created a few dashboards around the issues that have been found (which sites have the most, which assets have the most, etc.), but I can’t wrap my head around this one. I’ve been working with Power BI for a short while and have figured out a few things, but my brain isn’t connecting the dots on this one for me.
Any assistance would be appreciated. I think I can do a lot of interesting things with this data, just can't seem to get this one together.
If this isn’t the place for this, please let me know and I will happily remove it.
Anonymous To get a list of checks that have never been completed, you can do something like this:
Measure = VAR __MassterList = DISTINCT( 'Maintenance Check Master List'[MtcCheck] ) VAR __Completed = DISTINCT( 'Completed Maintenance Checks'[MtcCheck] ) VAR __Table = EXCEPT( __MasterList, __Completed ) VAR __Result = COUNTROWS( __Table ) RETURN __Result
3 Replies
Anonymous To get a list of checks that have never been completed, you can do something like this:
Measure = VAR __MassterList = DISTINCT( 'Maintenance Check Master List'[MtcCheck] ) VAR __Completed = DISTINCT( 'Completed Maintenance Checks'[MtcCheck] ) VAR __Table = EXCEPT( __MasterList, __Completed ) VAR __Result = COUNTROWS( __Table ) RETURN __ResultGreg_Deckler Thanks Greg! This helps!