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

Compare column value with previous row

Hi,

 

Im having a bit of struggle with DAX when trying to compare a value in a column to the previous row. So far, there doesnt seem to be a good answer that works for me. I have been trying to figure it out for a whole day now which is why i have decided to turn here for help.

What I am trying to do is to count switches from True-to-False and False-to-True for each different name. I need this to understand which names are the most flaky.
So the data would look something like this:

time                                   City                name                            success
4/19/2018 hh:mm:ss          LA                  Jill                                 True
4/19/2018 hh:mm:ss          CA                  Miranda                       True
4/19/2018 hh:mm:ss          CA                  Miranda                       False
4/19/2018 hh:mm:ss          CA                  Miranda                       False
4/20/2018 hh:mm:ss          LA                  Jill                                 True
4/20/2018 hh:mm:ss          LA                  Jill                                 False
4/20/2018 hh:mm:ss          LA                  Miranda                        True
4/20/2018 hh:mm:ss          LA                  Jill                                 False
4/21/2018 hh:mm:ss          LA                  Jill                                 True
4/21/2018 hh:mm:ss          CA                  Miranda                        False

So i guess there would be two ways - either to create a new columns that marks 0/False or 1/True whether there has been a change, or to just return the overall number for each name. For this example, the return should be something like this:

Name              Switches
Jill                    2
Miranda           3

Another thing is that both names can be in different cities, so it should be filterable by city, meaning if i take one city out, the switch would also not take into count the rows that have been filtered out. In this example the name Miranda would have -1 switch if i would take out LA.

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Compare column value with previous row

Hi @krtalvis,

 

Try this formula please. You can check out the demo in the attachment.

Measure 2 =
SUMX (
    SUMMARIZE (
        '180720',
        '180720'[time],
        '180720'[City],
        '180720'[name],
        '180720'[success],
        "switch",
        VAR lastTime =
            CALCULATE (
                MAX ( '180720'[time] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] < EARLIER ( '180720'[time] ) )
            )
        VAR lastSuccess =
            CALCULATE (
                MIN ( '180720'[success] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] = lastTime )
            )
        RETURN
            IF ( [success] = lastSuccess || ISBLANK ( lastSuccess ) = TRUE (), 0, 1 )
    ),
    [switch]
)

Compare_column_value_with_previous_row

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Compare column value with previous row

Hi @krtalvis,

 

Try this formula please. You can check out the demo in the attachment.

Measure 2 =
SUMX (
    SUMMARIZE (
        '180720',
        '180720'[time],
        '180720'[City],
        '180720'[name],
        '180720'[success],
        "switch",
        VAR lastTime =
            CALCULATE (
                MAX ( '180720'[time] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] < EARLIER ( '180720'[time] ) )
            )
        VAR lastSuccess =
            CALCULATE (
                MIN ( '180720'[success] ),
                FILTER ( ALLSELECTED ( '180720' ), '180720'[time] = lastTime )
            )
        RETURN
            IF ( [success] = lastSuccess || ISBLANK ( lastSuccess ) = TRUE (), 0, 1 )
    ),
    [switch]
)

Compare_column_value_with_previous_row

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
krtalvis Frequent Visitor
Frequent Visitor

Re: Compare column value with previous row

Had to do some few modifications to suit me even more, but this actually worked. Thanks ! Smiley Happy