Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I am trying to calculate the number/percentage of audits of a team as they are audited over a time period. I have been struggling with an efficient way to accomplish this task and I'm not making any more progress. I apologize in advance, because this is a long one. Here's what I'm working with:
My sources:
Table 1. Employee Roster:
Date | Employee ID | Department |
2/27 | Employee1 | Produce |
2/27 | Employee2 | Produce |
2/27 | Employee3 | Deli |
2/27 | Employee4 | Deli |
2/27 | Employee5 | Produce |
2/27 | Employee6 | Bakery |
2/27 | Employee7 | Bakery |
2/20 | Employee1 | Produce |
2/20 | Employee2 | Produce |
2/20 | Employee3 | Deli |
2/20 | Employee8 | Deli |
2/20 | Employee9 | Deli |
2/20 | Employee10 | Bakery |
I have a measure to calculate the number of employees in each department. I use COUNTROWS to return a roster size by department:
Measure Result - Department Employee Count:
Date | Department | # of Employees |
2/27 | Produce | 3 |
2/27 | Deli | 2 |
2/27 | Bakery | 2 |
2/20 | Produce | 2 |
2/20 | Deli | 3 |
2/20 | Bakery | 1 |
These employees are periodically audited for cleanliness.
Table 2. Audit Results:
Date | Employee | Department | Result |
2/27 | Employee1 | Produce | Pass |
2/21 | Employee4 | Deli | Pass |
2/20 | Employee2 | Produce | Pass |
2/19 | Employee8 | Deli | Fail |
2/15 | Employee7 | Bakery | Pass |
My desired outcome:
Slicers: Date Slicer (choose week/month/quarter)
Dates | Department | # of Employees (as of first date of measuring period) | Audited | Remaining |
2/1 - 2/27 | Deli | 3 | 2 | 1 |
2/1 - 2/27 | Bakery | 2 | 1 | 1 |
2/1 - 2/27 | Produce | 3 | 1 | 2 |
2/1 - 2/19 | Deli | 3 | 1 | 2 |
2/1 - 2/19 | Bakery | 2 | 1 | 1 |
2/1 - 2/19 | Produce | 3 | 0 | 3 |
My challenge(s):
Thank you for reading, and thank you for any guidance you may be able to provide!
@v-yangliu-msft Can you provide any more clarification on your proposed solution? I would greatly appreciate it!
@v-yangliu-msftHello and thank you for the answer! I'm trying to work my way through the steps and I've encountered a couple of error messages:
In the "Add a week and count to Audit Results" step, the following code:
count = COUNTX(FILTER(ALL('Audit Results'),'Audit Results'[week]=EARLIER('Audit Results'[week])&&'Audit Results'[Department]=EARLIER('Audit Results'[Department])),[Department])
returns an error that says, "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."
Secondly, when I use this code:
Audited = var _min=MINX(ALLSELECTED('Audit Results'),'Audit Results'[Date]) var _max=MaxX(ALLSELECTED('Audit Results'),'Audit Results'[Date]) return CALCULATE(SUM('Audit Results'[Column]),FILTER(ALL('Audit Results'),MAX('Audit Results'[week])>=WEEKNUM(_min,1)&&MAX('Audit Results'[week])<=WEEKNUM(_max,1)&&'Audit Results'[Department]=MAX('Employee Roster'[Department])))
can you clarify which column 'Audit Results'[Column] should use?
Thank you for your time and assistance! I am very greatful!
Hi @bigchippah ,
Here are the steps you can follow:
1. Create calculated table.
Table = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))
2. Create calculated column.
Add a week and count to Audit Results:
week = WEEKNUM('Employee Roster'[Date],1)
count = COUNTX(FILTER(ALL('Audit Results'),'Audit Results'[week]=EARLIER('Audit Results'[week])&&'Audit Results'[Department]=EARLIER('Audit Results'[Department])),[Department])
Add a week to Employee Roster:
week = WEEKNUM('Employee Roster'[Date],1)
3. Create measure.
# of Employees =
var _min=MINX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
var _max=MaxX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
return
COUNTX(FILTER(ALL('Employee Roster'),
'Employee Roster'[week] >=WEEKNUM(_min,1)&&
'Employee Roster'[week] <=WEEKNUM(_max,1)&&
'Employee Roster'[Department]=MAX('Employee Roster'[Department])&&
'Employee Roster'[week]=MAX('Employee Roster'[week])
),[Department])
Audited =
var _min=MINX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
var _max=MaxX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
return
CALCULATE(SUM('Audit Results'[Column]),FILTER(ALL('Audit Results'),MAX('Audit Results'[week])>=WEEKNUM(_min,1)&&MAX('Audit Results'[week])<=WEEKNUM(_max,1)&&'Audit Results'[Department]=MAX('Employee Roster'[Department])))
4. Result:
Take the [Date] of the Table table as the slicer.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
120 | |
74 | |
72 | |
58 | |
48 |
User | Count |
---|---|
167 | |
83 | |
68 | |
66 | |
55 |