Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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?

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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