How to count the number of transitions from 0 to 1 in a column

[pabeader]

I would like to know a way to count the number of times that the value in a column, transitions from 0 to 1.

I'm not even sure where to start on this one.  I'm trying to stay in DirectQuery for realtime analysis so that limits the fancy things we can try like adding index columns.

Any ideas would be greatly appriciated.  Even an idea of where to start would be great.

[pabeader]

I guess I'm not that smart afterall.  Even with great hints I still can't figure out how to do this.  

[mahoneypat]

Don't beat yourself up.  This is not an easy measure to write.  Please try this expression.  It returns 3 with your sample data.

StatusChanges 0 to 1 =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            'Status',
            'Status'[Timestamp],
            'Status'[Status]
        ),
        "cPrevStatus",
            VAR thistime = 'Status'[Timestamp]
            VAR prevtime =
                CALCULATE (
                    MAX ( 'Status'[Timestamp] ),
                    ALL (
                        'Status'[Timestamp],
                        'Status'[Status]
                    ),
                    'Status'[Timestamp] < thistime
                )
            RETURN
                CALCULATE (
                    MIN ( 'Status'[Status] ),
                    ALL (
                        'Status'[Timestamp],
                        'Status'[Status]
                    ),
                    'Status'[Timestamp] = prevtime
                )
    )
RETURN
    COUNTROWS (
        FILTER (
            summary,
            AND (
                'Status'[Status] = 1,
                [cPrevStatus] = 0
            )
        )
    )

Regards,

Pat

[pabeader]

When I put it into my application it never finishes updating.  The actual datatable is like 100,000 rows.  Not sure how that is going effect things.   Next I'm going to try with a smaller version of the dataset.

[mahoneypat]

I figured it was going to be slow (lots of calculation going on), so I planned to send a better version.  Your last post confirmed it was needed.  Here is a more efficient version (but still slow).  I made a table with 10,000 rows to test it out with random 1s and 0s and it took 13s on my computer to calculate, so 100,000 may take 10X that (or may time out).  

I don't use Direct Query much, so I don't know if the query could do it, but I would also try that route.  You can sort by timestamp, add an index, and then use that index to reference the previous row in a custom column to get that value on each row.

StatusChanges New =
VAR alltimestamps =
    SUMMARIZE (
        'Status',
        'Status'[Timestamp],
        'Status'[Status]
    )
VAR just1s =
    FILTER (
        alltimestamps,
        'Status'[Status] = 1
    )
VAR prev0 =
    FILTER (
        just1s,
        VAR thistime = 'Status'[Timestamp]
        RETURN
            CALCULATE (
                SUM ( 'Status'[Status] ),
                TOPN (
                    2,
                    FILTER (
                        alltimestamps,
                        'Status'[Timestamp] <= thistime
                    ),
                    'Status'[Timestamp], DESC
                )
            ) = 1
    )
RETURN
    COUNTROWS ( prev0 )

Regards,

Pat

[pabeader]

So what I've found is if there are more than around 3000 rows, it throws an error.  Thanks for all your help, but I think I'm going to have to redesign the program.  

[pabeader]

Wow!!!  No wonder I couldn't figure it out!  Now I get to try to understand it.  Thank you ever so much.

[amitchandak]

@pabeader ,Can you share sample data and sample output in table format?

[pabeader]

Timestamp          |Status

11/22/20 9:00:00 |0
11/22/20 9:00:01 |0
11/22/20 9:00:02 |0
11/22/20 9:00:03 |1
11/22/20 9:00:04 |1
11/22/20 9:00:05 |1
11/22/20 9:00:06 |1
11/22/20 9:00:07 |0
11/22/20 9:00:08 |0
11/22/20 9:00:09 |0
11/22/20 9:00:10 |0
11/22/20 9:00:11 |0
11/22/20 9:00:12 |1
11/22/20 9:00:13 |1
11/22/20 9:00:14 |0
11/22/20 9:00:15 |0
11/22/20 9:00:16 |0
11/22/20 9:00:17 |1
11/22/20 9:00:18 |1
11/22/20 9:00:19 |0

Answer is 3

[mahoneypat]

Do you have an index or a datetime column to decide which one is "previous"?  To do this, you could make a column expression with two variables - one to get the value on the current row and one to get the previous value.  Then make an IF with an AND to check if the current value is 1 and the prev was 0.  You can then SUM that new column to get the count. 

If needed in a measure, you could use the same approach in a virtual table you create in a variable inside the measure.

Regards,

Pat

[pabeader]

I do have a Datetime column.  I will give that a go and see what happens.  I know that I tried some things using that column but I would get the "switching to import" warning.