Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
swong89
Frequent Visitor

Counting consecutive status by data and employee and status

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.

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

Hi @swong89 ,

 

Try the formula like below:

Column =
RANKX (
    FILTER ( 'Table', 'Table'[Employee] = EARLIER ( 'Table'[Employee] ) ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

vhenrykmstf_0-1643091625887.png

Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-henryk-mstf 

 

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?

swong89
Frequent Visitor

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.

 

DateEmployeeTourJob NumberPulled?Result: Counter
1/22/2022Person 1NTNC000611
1/17/2022Person 2NTNC000611
1/18/2022Person 2NTNC000612
1/21/2022Person 2NTNC000613
1/19/2022Person 3AMNC001311
1/21/2022Person 4AMNC001511
1/16/2022Person 5AMNC002011
1/23/2022Person 6AMNC002311
1/17/2022Person 7NTNC002511
1/23/2022Person 7NTNC002512
1/17/2022Person 8PMNC0029A11
1/22/2022Person 9PMNC0029A11
1/18/2022Person 10PMNC003211
1/19/2022Person 10PMNC003212
1/20/2022Person 10PMNC003213
1/21/2022Person 10PMNC003214
1/22/2022Person 10PMNC003215

 

Screenshot of the data exported into Excel

Screenshot 2022-01-24 174320.png

v-henryk-mstf
Community Support
Community Support

Hi @swong89 ,

 

Based on your description, I don't seem to understand much about the needs you describe in this paragraph.

       vhenrykmstf_0-1642991397827.png

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.