Forum Discussion

stefanycheck's avatar
stefanycheck
Frequent Visitor
8 years ago
Solved

Count everytime a status change

Hi!

 

I have this data bellow where "sentido" is the column of status. And "despejando produto"(let's call it status 1) and "mechendo produto" (let's call it status 2) are the two possible status.

 

I want to be able to count the number of status 1 and 2 but everytime it changes from one to another. Like, if there is status 1, status 1, status 1, status 2, status 2, status 1, status 1, I want the expression to tell that there were two status 1 and 1 status 2. I'm not sure if I'm clear enough. 

I tried to make a column like this 

Count = if(Consulta1[sentido]=(LOOKUPVALUE(Consulta1[sentido];Consulta1[Índice];Consulta1[Índice]-1));"";1) and them try to count the ones, but it's not working. Hope someone can help!! I can try to explain it better if you have any questions

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    stefanycheck

     

    Try this calculated column

     

    EarliestIndex =
VAR Temp =
    CALCULATE (
        MAX ( [Index] ),
        FILTER (
            Table1,
            [Index] < EARLIER ( [Index] )
                && [STATUS] <> EARLIER ( [STATUS] )
        )
    )
VAR Temp1 =
    CALCULATE (
        MIN ( [Index] ),
        FILTER (
            Table1,
            [Index] > EARLIER ( [Index] )
                && [STATUS] <> EARLIER ( [STATUS] )
        )
    )
RETURN
    IF ( [Index] > temp && OR ( [Index] < temp1, ISBLANK ( temp1 ) ), temp + 1, 0 )

    Then put the distinct count of this column along with the STATUS in a TABLE visual

     

     

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    This seems like an EARLIER problem. Sample source data that can be copied and pasted would help. 

     

    See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

     

    Also...Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    • stefanycheck's avatar
      stefanycheck
      Frequent Visitor
      8 years ago
      STATUSIndex
      status11
      status12
      status13
      status14
      status25
      status26
      status17
      status18
      status19
      status110
      status211
      status212
      status213
      status114

       

      Here's the sample data! I want something (a column or a measure, idk) that when I use it on the visuals, shows me that during that day, for example, I had 3 status 1 and 2 status 2, instead of 9 status 1 and 5 status 2, which would be just counting the number of times they appeared - which is exactly what I don't want it to do.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        stefanycheck

         

        Try this calculated column

         

        EarliestIndex =
VAR Temp =
    CALCULATE (
        MAX ( [Index] ),
        FILTER (
            Table1,
            [Index] < EARLIER ( [Index] )
                && [STATUS] <> EARLIER ( [STATUS] )
        )
    )
VAR Temp1 =
    CALCULATE (
        MIN ( [Index] ),
        FILTER (
            Table1,
            [Index] > EARLIER ( [Index] )
                && [STATUS] <> EARLIER ( [STATUS] )
        )
    )
RETURN
    IF ( [Index] > temp && OR ( [Index] < temp1, ISBLANK ( temp1 ) ), temp + 1, 0 )

        Then put the distinct count of this column along with the STATUS in a TABLE visual