cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

 data.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Count everytime a status change

@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

 

 

Try my new Power BI game Cross the River

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Count everytime a status change

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

 

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Count everytime a status change

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.

Highlighted
Super User III
Super User III

Re: Count everytime a status change

@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

 

 

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Super User III
Super User III

Re: Count everytime a status change

@stefanycheck

 

Please see attached file

 

countevery.png

Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

Re: Count everytime a status change

It worked!!! Thanks a lot!

Highlighted
Frequent Visitor

Re: Count everytime a status change

@Zubair_Muhammad if it isn't too much, could you also tell me how I can show this status along time? I have a column with data and time for each time this status appeared. There is a way that I can show like dots in each time with each status??

Highlighted
Super User III
Super User III

Re: Count everytime a status change

@stefanycheck

 

Please could you post some sample data with time and draw your expected output?

Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

Re: Count everytime a status change

@Zubair_Muhammad 

 

Actually, I could figure it out. But now, I want to make two columns, one with the date and time a status started and one with the date and time it ends. For example, now, my table looks like this: 

 

DateStatusEarliestIndexindex
8/8/18 14:00Status 100
8/8/18 14:01Status 111
8/8/18 14:02Status 112
8/8/18 14:03Status 113
8/8/18 14:04Status 114
8/8/18 14:05Status 255
8/8/18 14:06Status 256
8/8/18 14:07Status 257
8/8/18 14:08Status 188
8/8/18 14:09Status 189
8/8/18 14:10Status 1810
8/8/18 14:11Status 1811
8/8/18 14:12Status 21212
8/8/18 14:13Status 21213
8/8/18 14:14Status 21214
8/8/18 14:15Status 21215
8/8/18 14:16Status 11616
8/8/18 14:17Status 11617
8/8/18 14:18Status 11618
8/8/18 14:19Status 11619

 

And I want it to look like this:

 

DateStatusEarliestIndexindexStartsEnds
8/8/18 14:00Status 1108/8/18 14:008/8/18 14:04
8/8/18 14:01Status 1118/8/18 14:008/8/18 14:04
8/8/18 14:02Status 1128/8/18 14:008/8/18 14:04
8/8/18 14:03Status 1138/8/18 14:008/8/18 14:04
8/8/18 14:04Status 1148/8/18 14:008/8/18 14:04
8/8/18 14:05Status 2658/8/18 14:058/8/18 14:07
8/8/18 14:06Status 2668/8/18 14:058/8/18 14:07
8/8/18 14:07Status 2678/8/18 14:058/8/18 14:07
8/8/18 14:08Status 1988/8/18 14:088/8/18 14:11
8/8/18 14:09Status 1998/8/18 14:088/8/18 14:11
8/8/18 14:10Status 19108/8/18 14:088/8/18 14:11
8/8/18 14:11Status 19118/8/18 14:088/8/18 14:11
8/8/18 14:12Status 213128/8/18 14:128/8/18 14:15
8/8/18 14:13Status 213138/8/18 14:128/8/18 14:15
8/8/18 14:14Status 213148/8/18 14:128/8/18 14:15
8/8/18 14:15Status 213158/8/18 14:128/8/18 14:15
8/8/18 14:16Status 117168/8/18 14:168/8/18 14:19
8/8/18 14:17Status 117178/8/18 14:168/8/18 14:19
8/8/18 14:18Status 117188/8/18 14:168/8/18 14:19
8/8/18 14:19Status 117198/8/18 14:168/8/18 14:19

 

So I can make some kind of report with it, showing the time each change on status began and each time it ended.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors