cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrmiyagi
Helper III
Helper III

Frequency of work based on set standards

Hello! I could use some guidance on creating a measure and KPI that can count (distinct) the number of frequencies and calculate if it has reached or not from a set standards. The frequency could be weekly, monthly, or yearly and the standard is the amount of times it needs to meet. The table of maintenance standards can be changed if needed as this was created manually and open to all suggestions, thank you! 

 

Tables:

WQPF_Labor

WO NUMBERBooked Employee NamePM IDHOURSLabor Cost
696854Smith, JohnPM-WQPF-012          0.5$24.02
696854Smith, JohnPM-WQPF-012          0.5$0.00
696853Smith, JohnPM-WQPF-012          0.5$24.02
696853Smith, JohnPM-WQPF-012          0.5$0.00
696852Smith, JohnPM-WQPF-012          0.5$24.02
696852Smith, JohnPM-WQPF-012          0.5$0.00
696719Smith, JohnPM-WQPF-019           4$55.80
696719Smith, JohnPM-WQPF-019           4 $159.08
696719Smith, JohnPM-WQPF-019          2.5$0.00
696705Smith, JohnPM-WQPF-018          8$318.16
696705Smith, JohnPM-WQPF-018         40$1,425.60
696705Smith, JohnPM-WQPF-018         40$1,425.60
696691Smith, JohnPM-WQPF-017          1$48.03
696667Smith, JohnPM-WQPF-016          1$48.03

 

Maintenance_Standard

PM IDStandardFrequencyStatus
 PM-WQPF-012      1MONTHLYIn-Service
 PM-WQPF-013      1MONTHLYIn-Service
 PM-WQPF-014      1MONTHLYIn-Service
 PM-WQPF-015      1MONTHLYIn-Service
 PM-WQPF-016      6YEARLYIn-Service
 PM-WQPF-017      6YEARLYIn-Service
 PM-WQPF-018      6YEARLYIn-Service
 PM-WQPF-019      6YEARLYIn-Service
 PM-WQPF-020      6YEARLYIn-Service
 PM-WQPF-021      6YEARLYIn-Service

 

pmid.PNG

 

Desired Output: 

PM IDDistinct CountKPI
PM-WQPF-012       1PASS
PM-WQPF-016       4FAIL

 

2 REPLIES 2
lbendlin
Super User III
Super User III

I don't understand this part: "count (distinct) the number of frequencies".

Note that [PM ID] in your second table has an extra leading space.

I would also strongly recommend against a M:M relationship. Please explain why it is necessary.

I need to find how many times a WO is on the labor table from the PM ID. The frequency is the maintenance standard so for example the PM ID must have been completed 5 times in a month period. 

How can I fix the M:M relationship as there are many PM IDs on both tables? 


Thank I will fix the extra leading space! 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors