Forum Discussion
Count of Status
Hi All.
I have a requirement to calculate the number of time a submission number has toggled between different teams for two scenarios.
Data below:
I need to derive two columns based on the below scenarios
Scenario 1: Overall Toggle count
I need to get the number of times a submission number has toggled between teams i.e column U.
Eg : For submission number : count is 5
Cell U3, U4 (U 3 and U4 - same team ) to U5 = 1 time (IRB Staff to Study Team)
U5 to U 6 = 1 time ( Study team to IRB Staff)
U6 to U7 = 1 time (IRB Staff to Study Team)
U7 to U8 U9 U11 = 1 time ( Study team to IRB Staff)
U11 to U 12 U 13 = 1 time (IRB Staff to Study Team)
Total 5 times
Scenario 2: Toggle Count based on Pre-Review/IRB- Review
I need to calculate the number of times a submission number has toggled between teams i.e column U based on Pre-Review/IRB- Review( Column S)
For Pre-Review Only :
Eg : For the first submission number, for Pre-Review type : count is 4
Cell U3, U4 (U 3 and U4 - same team ) to U5 = 1 time (IRB Staff to Study Team)
U5 to U 6 = 1 time ( Study team to IRB Staff)
U6 to U7 = 1 time (IRB Staff to Study Team)
U7 to U8 = 1 time ( Study team to IRB Staff)
Total 4 times
Same has to be derived for IRB Review where count is 1
U9 U10 to U12 U 13 = 1 time (IRB Staff to Study Team)
Sample data:
| Submission Number | Workflow Status | Workflow Status Date | Pre-Review/IRB- Review | IRB Staff/Study Team | Overall Toggle Count | Toggle Count based on Pre-Review/IRB- Review |
| 0005004414-03 | Routing | 1/30/2020 | FALSE | FALSE | 5 | |
| 0005004414-03 | Assigning for Prereview | 1/31/2020 | Pre-Review | IRB Staff | 5 | 4 |
| 0005004414-03 | IRB Staff Pre-Review | 2/3/2020 | Pre-Review | IRB Staff | 5 | 4 |
| 0005004414-03 | Pre-Review Modifications Required | 2/27/2020 | Pre-Review | Study Team | 5 | 4 |
| 0005004414-03 | Pre-Review Modifications Response | 2/27/2020 | Pre-Review | IRB Staff | 5 | 4 |
| 0005004414-03 | Pre-Review Modifications Required | 2/28/2020 | Pre-Review | Study Team | 5 | 4 |
| 0005004414-03 | Pre-Review Modifications Response | 2/28/2020 | Pre-Review | IRB Staff | 5 | 4 |
| 0005004414-03 | Assigned to Agenda | 3/3/2020 | IRB Review | IRB Staff | 5 | 1 |
| 0005004414-03 | Approved | 3/19/2020 | FALSE | FALSE | 5 | |
| 0005004414-03 | IRB Staff Pre-Review | 2/28/2020 | IRB Review | IRB Staff | 5 | 1 |
| 0005004414-03 | Pre-Review Modifications Required | 3/3/2020 | IRB Review | Study Team | 5 | 1 |
| 0005004414-03 | Pre-Review Modifications Response | 3/19/2020 | IRB Review | Study Team | 5 | 1 |
Can some one please let me know how to derive both these columns in Power BI .
Thank you
Poojitha
Anonymous See attached, Table (24). Toggle Index totals to 5, that's the overall toggle. Toggle Index Team has four 1's for Prereview and one 1 for IRB Review. Is this what you are looking for?
14 Replies
- Greg_Deckler
Community Champion
Anonymous - OK, so I got this far and then realized that you are going to need an Index. See attached PBIX file attached below sig. Table (24). The reason you need an index is because you have things switching on the same day so it makes it difficult/impossible to know which one is before another one. Is it possible for you to have an Index on this data that represents what happened first?
Toggled = VAR __PreviousDate = MAXX ( FILTER ( 'Table (24)', [Workflow Status Date] < EARLIER ( [Workflow Status Date] ) && [Submission Number] = EARLIER ( [Submission Number] ) && [IRB Staff/Study Team] <> "FALSE" ), [Workflow Status Date] ) VAR __PreviousTeam = MAXX ( FILTER ( 'Table (24)', [Workflow Status Date] = __PreviousDate && [Submission Number] = EARLIER ( [Submission Number] ) ), [IRB Staff/Study Team] ) RETURN IF ( [IRB Staff/Study Team] = "FALSE" || ISBLANK ( __PreviousTeam ) || __PreviousTeam = [IRB Staff/Study Team], 0, 1 )Thank you for the detailed help. I will try the same on the actual dataset and update.
Also can you please let me know if Index has to be added? If yes, could you please help provide the code with the Index field.
Thank you
Poojitha
- Greg_Deckler
Anonymous Sure, updated PBIX attached same table.
Toggled Index = VAR __PreviousIndex = MAXX ( FILTER ( 'Table (24)', [Index] < EARLIER ( [Index] ) && [Submission Number] = EARLIER ( [Submission Number] ) && [IRB Staff/Study Team] <> "FALSE" ), [Index] ) VAR __PreviousTeam = MAXX ( FILTER ( 'Table (24)', [Index] = __PreviousIndex && [Submission Number] = EARLIER ( [Submission Number] ) ), [IRB Staff/Study Team] ) RETURN IF ( [IRB Staff/Study Team] = "FALSE" || ISBLANK ( __PreviousTeam ) || __PreviousTeam = [IRB Staff/Study Team], 0, 1 )
Try Time in State for Azure DevOps extension https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure .
Using it, you don't need to upload data to Power BI. Reports will be generated automatically based on your Queries. You can also export them to Excel.Available reports:
- Time in State
- State Count
- Transition Count
