Forum Discussion

krtalvis's avatar
krtalvis
Frequent Visitor
8 years ago
Solved

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

  • 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

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    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

    • krtalvis's avatar
      krtalvis
      Frequent Visitor

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

       

    • SherryZhao's avatar
      SherryZhao
      Frequent Visitor

      hi Dale, I want to know how could i get the row of "success" in Dax? Thanks in advance.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Creation required to power bi:
      When I try to compare a value in a column to the row before it, I'm finding it a little difficult with DAX. I'm having trouble finding a satisfactory solution at this time. I chose to come here for assistance because I had been attempting to figure it out for more than a week. Thanks in advance!

      Counting switches from Training for each employee's name, date, and financial quarter is what I'm attempting to accomplish. (Dont consider switches counting in Leave and Absent.) We have multiple training, this is for sample data.
      As a result, the data would resemble this:
      Note: Source file for Columns A through C; output for Columns D and E is required.

      EMP_NameDateTrainingSwitch Count by MonthSwitch Count by Quarter

      Prabaharan8-Jan-23Excel00
      Prabaharan14-Jan-23SQL11
      Prabaharan18-Jan-23Power bi11
      Prabaharan18-Jan-23Leave00
      Prabaharan20-Jan-23Leave00
      Prabaharan25-Jan-23Excel11
      Prabaharan8-Feb-23Excel00
      Prabaharan14-Feb-23SQL11
      Prabaharan18-Feb-23Power bi11
      Prabaharan18-Feb-23Leave00
      Prabaharan20-Feb-23Absent00
      Prabaharan25-Feb-23Power bi00
      Prabaharan8-Mar-23Excel01
      Prabaharan14-Mar-23Excel00
      Prabaharan18-Mar-23Excel00
      Prabaharan18-Mar-23Excel00
      Prabaharan20-Mar-23Excel00
      Prabaharan25-Mar-23Excel00


      Screenshot: 

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    Can you please share the my request, output power bi

    Thanks in Advance!