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 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
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |