Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 NUMBER | Booked Employee Name | PM ID | HOURS | Labor Cost |
696854 | Smith, John | PM-WQPF-012 | 0.5 | $24.02 |
696854 | Smith, John | PM-WQPF-012 | 0.5 | $0.00 |
696853 | Smith, John | PM-WQPF-012 | 0.5 | $24.02 |
696853 | Smith, John | PM-WQPF-012 | 0.5 | $0.00 |
696852 | Smith, John | PM-WQPF-012 | 0.5 | $24.02 |
696852 | Smith, John | PM-WQPF-012 | 0.5 | $0.00 |
696719 | Smith, John | PM-WQPF-019 | 4 | $55.80 |
696719 | Smith, John | PM-WQPF-019 | 4 | $159.08 |
696719 | Smith, John | PM-WQPF-019 | 2.5 | $0.00 |
696705 | Smith, John | PM-WQPF-018 | 8 | $318.16 |
696705 | Smith, John | PM-WQPF-018 | 40 | $1,425.60 |
696705 | Smith, John | PM-WQPF-018 | 40 | $1,425.60 |
696691 | Smith, John | PM-WQPF-017 | 1 | $48.03 |
696667 | Smith, John | PM-WQPF-016 | 1 | $48.03 |
Maintenance_Standard
PM ID | Standard | Frequency | Status |
PM-WQPF-012 | 1 | MONTHLY | In-Service |
PM-WQPF-013 | 1 | MONTHLY | In-Service |
PM-WQPF-014 | 1 | MONTHLY | In-Service |
PM-WQPF-015 | 1 | MONTHLY | In-Service |
PM-WQPF-016 | 6 | YEARLY | In-Service |
PM-WQPF-017 | 6 | YEARLY | In-Service |
PM-WQPF-018 | 6 | YEARLY | In-Service |
PM-WQPF-019 | 6 | YEARLY | In-Service |
PM-WQPF-020 | 6 | YEARLY | In-Service |
PM-WQPF-021 | 6 | YEARLY | In-Service |
Desired Output:
PM ID | Distinct Count | KPI |
PM-WQPF-012 | 1 | PASS |
PM-WQPF-016 | 4 | FAIL |
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |