Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all.
I have a dataset from a daily survey on attendance. Some of the fields are date, name of the employee, and status, and they can be entered in any order. The status is a Yes/No toggle on PowerApps (aka True/False in PowerBI), letting us know if an employee has been routed to another assignment. An employee may be "pulled"/re-assigned as needed.
Output: We are trying to see if we can count the number of days an employee was pulled consecutively. The counter/flag would start counting each day until the "pulled" status drops off. The counter restarts again with more than 1 consecutive re-assignment.
All three fields are normalized in one SharePoint list. The size is up to 975 entries a day.
I tried writing a code, but it seems not doable on PowerBI as a running report.
Hi @swong89 ,
Try the formula like below:
Column =
RANKX (
FILTER ( 'Table', 'Table'[Employee] = EARLIER ( 'Table'[Employee] ) ),
'Table'[Date],
,
ASC,
DENSE
)
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry. I just got back to this project. I tried your formula, but it does not seem to filter the "Pulled" column filter. The Pulled column has values of "0" and "1", with "1" as being true. How do I include the "1" into the rankx formula?
Hi @v-henryk-mstf .
I've included some data below based on the data we have collected so far. The data is already filtered for the Pulled indicator (Column 5). Every time the employee has a pulled indicator =1 means they were re-assigned. Whenever that is triggered, I want to start counting the number of times an employee is consecutively by date with the 1 (last column). The counter would restart when that condition no longer exists.
Date | Employee | Tour | Job Number | Pulled? | Result: Counter |
1/22/2022 | Person 1 | NT | NC0006 | 1 | 1 |
1/17/2022 | Person 2 | NT | NC0006 | 1 | 1 |
1/18/2022 | Person 2 | NT | NC0006 | 1 | 2 |
1/21/2022 | Person 2 | NT | NC0006 | 1 | 3 |
1/19/2022 | Person 3 | AM | NC0013 | 1 | 1 |
1/21/2022 | Person 4 | AM | NC0015 | 1 | 1 |
1/16/2022 | Person 5 | AM | NC0020 | 1 | 1 |
1/23/2022 | Person 6 | AM | NC0023 | 1 | 1 |
1/17/2022 | Person 7 | NT | NC0025 | 1 | 1 |
1/23/2022 | Person 7 | NT | NC0025 | 1 | 2 |
1/17/2022 | Person 8 | PM | NC0029A | 1 | 1 |
1/22/2022 | Person 9 | PM | NC0029A | 1 | 1 |
1/18/2022 | Person 10 | PM | NC0032 | 1 | 1 |
1/19/2022 | Person 10 | PM | NC0032 | 1 | 2 |
1/20/2022 | Person 10 | PM | NC0032 | 1 | 3 |
1/21/2022 | Person 10 | PM | NC0032 | 1 | 4 |
1/22/2022 | Person 10 | PM | NC0032 | 1 | 5 |
Screenshot of the data exported into Excel
Hi @swong89 ,
Based on your description, I don't seem to understand much about the needs you describe in this paragraph.
Could you provide the relevant test data and screenshots of the expected results so that I can further answer for you? Also you can ask for help in the powapp community or have a better solution.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Looking forward to your feedback.
Best Regards,
Henry
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |