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. ...
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

     

     

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