Forum Discussion
Count everytime a status change
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
| 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
It worked!!! Thanks a lot!
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??