Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
STATUS | Index |
status1 | 1 |
status1 | 2 |
status1 | 3 |
status1 | 4 |
status2 | 5 |
status2 | 6 |
status1 | 7 |
status1 | 8 |
status1 | 9 |
status1 | 10 |
status2 | 11 |
status2 | 12 |
status2 | 13 |
status1 | 14 |
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.
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
Please see attached file
@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??
Please could you post some sample data with time and draw your expected output?
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:
Date | Status | EarliestIndex | index |
8/8/18 14:00 | Status 1 | 0 | 0 |
8/8/18 14:01 | Status 1 | 1 | 1 |
8/8/18 14:02 | Status 1 | 1 | 2 |
8/8/18 14:03 | Status 1 | 1 | 3 |
8/8/18 14:04 | Status 1 | 1 | 4 |
8/8/18 14:05 | Status 2 | 5 | 5 |
8/8/18 14:06 | Status 2 | 5 | 6 |
8/8/18 14:07 | Status 2 | 5 | 7 |
8/8/18 14:08 | Status 1 | 8 | 8 |
8/8/18 14:09 | Status 1 | 8 | 9 |
8/8/18 14:10 | Status 1 | 8 | 10 |
8/8/18 14:11 | Status 1 | 8 | 11 |
8/8/18 14:12 | Status 2 | 12 | 12 |
8/8/18 14:13 | Status 2 | 12 | 13 |
8/8/18 14:14 | Status 2 | 12 | 14 |
8/8/18 14:15 | Status 2 | 12 | 15 |
8/8/18 14:16 | Status 1 | 16 | 16 |
8/8/18 14:17 | Status 1 | 16 | 17 |
8/8/18 14:18 | Status 1 | 16 | 18 |
8/8/18 14:19 | Status 1 | 16 | 19 |
And I want it to look like this:
Date | Status | EarliestIndex | index | Starts | Ends |
8/8/18 14:00 | Status 1 | 1 | 0 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:01 | Status 1 | 1 | 1 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:02 | Status 1 | 1 | 2 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:03 | Status 1 | 1 | 3 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:04 | Status 1 | 1 | 4 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:05 | Status 2 | 6 | 5 | 8/8/18 14:05 | 8/8/18 14:07 |
8/8/18 14:06 | Status 2 | 6 | 6 | 8/8/18 14:05 | 8/8/18 14:07 |
8/8/18 14:07 | Status 2 | 6 | 7 | 8/8/18 14:05 | 8/8/18 14:07 |
8/8/18 14:08 | Status 1 | 9 | 8 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:09 | Status 1 | 9 | 9 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:10 | Status 1 | 9 | 10 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:11 | Status 1 | 9 | 11 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:12 | Status 2 | 13 | 12 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:13 | Status 2 | 13 | 13 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:14 | Status 2 | 13 | 14 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:15 | Status 2 | 13 | 15 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:16 | Status 1 | 17 | 16 | 8/8/18 14:16 | 8/8/18 14:19 |
8/8/18 14:17 | Status 1 | 17 | 17 | 8/8/18 14:16 | 8/8/18 14:19 |
8/8/18 14:18 | Status 1 | 17 | 18 | 8/8/18 14:16 | 8/8/18 14:19 |
8/8/18 14:19 | Status 1 | 17 | 19 | 8/8/18 14:16 | 8/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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |