Count of Status

[Anonymous]

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

[Iryna_K]

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

[Greg_Deckler]

@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
    )

[Anonymous]

@Greg_Deckler 

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
    )

[Anonymous]

@Greg_Deckler 

I tried to use the logic from the pbix file you shared, but the field is not matching with the values as expected.

Please note , we need to derive two columns in PBI which is "Overall Toggle Count" and "Toggle Count based on Pre-Review and IRB Review".

These two columns which are available in the file is for refrence.

Thanks

Poojitha

[Anonymous]

Can someone please help on fixing this

[Greg_Deckler]

@Anonymous I need to understand your expected results. 

You state:

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

4 is the number that Toggled returns as you show and is highlighted. So, first, is Toggled flagging the right rows or not?

[Anonymous]

@Greg_Deckler 

We are deriving two columns, 

1. "Overall Toggle count" should be irrespective of "Pre-Review" and "IRB Review".

2. "Toggle count based on Pre-Review/IRB Review" should be for "Pre-Review" and "IRB Review" Separately.

[Greg_Deckler]

@Anonymous OK, so Overall Toggle count should be 4 then? Is that correct?

[Anonymous]

@Greg_Deckler 

Overall toggle count should be 5 as we are checking the toggle irrespective of Pre-REview/IRB Review.

Whereas for Toggle count based on Pre-Review/IRB-Review, the count will be 4 when filtered for "Pre-Review"

[Greg_Deckler · Accepted solution]

@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?

[Anonymous]

@Greg_Deckler 
Thank you, I will test with the entire dataset and update.

[Anonymous]

@Greg_Deckler 

Is there any way to make this dynamic based on the slicer selections. Since we have derived the index at the power query editor, the toggle values are fixed.

[Anonymous]

Hi @Greg_Deckler 

Thanks for getting into details.

Yes, the above is true for "Pre-Review". The toggled count=4 should be against "Pre- Review".

Same for "IRB Preview"