Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stefanycheck
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

@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

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

@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

 

 


Regards
Zubair

Please try my custom visuals

@stefanycheck

 

Please see attached file

 

countevery.png


Regards
Zubair

Please try my custom visuals

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

@stefanycheck

 

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


Regards
Zubair

Please try my custom visuals

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

It worked!!! Thanks a lot!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.