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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors