Forum Discussion

ahsan005's avatar
ahsan005
Frequent Visitor
3 years ago
Solved

Compare Previous Two Rows with a Current Row using DAX

Hi Everyone,   I have to create a new column and am trying to compare rows in a single column when a particular value exists in any of the rows. For e.g.   If Table[category] = "Site Down", then ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    There are newer DAX functions that are similar to SQL's LAG. For example, OFFSET:

     

    IsSiteDown =
VAR Summary =
    SUMMARIZE (
        Table2,
        Table2[sitename],
        Table2[category],
        Table2[firstoccurrence]
    )
VAR Prev1Cat =
    MAXX (
        OFFSET (
            -1,
            Summary,
            ORDERBY ( Table2[firstoccurrence] ),
            PARTITIONBY ( Table2[sitename] )
        ),
        Table2[category]
    )
VAR Prev2Cat =
    MAXX (
        OFFSET (
            -2,
            Summary,
            ORDERBY ( Table2[firstoccurrence] ),
            PARTITIONBY ( Table2[sitename] )
        ),
        Table2[category]
    )
VAR Result =
    IF (
        Table2[category] = "Site Down"
            && Prev1Cat = "Low Voltage"
            && Prev2Cat = "AC Mains Failure",
        "Yes",
        "No"
    )
RETURN
    Result

     

AlB's avatar
AlB
Community Champion
3 years ago

Hi ahsan005 

If you are going by firstoccurence to establish an order and look for the previous rows, what happens when the datetime in firstoccurence is exactly the same? This happens several times in your sample data. One option would be to sort as required in PQ and add an index column, then base it all on that index instead of on firstoccurence Pending that, try this for your column. See it all at work in the attached file.

 

 

NewColumn =
VAR previous_ =
    CALCULATE (
        MAX ( Table1[firstoccurrence] ),
        Table1[firstoccurrence] < EARLIER ( Table1[firstoccurrence] ),
        ALLEXCEPT ( Table1, Table1[sitename] )
    )
VAR previous2Prev_ =
    CALCULATE (
        MAX ( Table1[firstoccurrence] ),
        Table1[firstoccurrence] < previous_,
        ALLEXCEPT ( Table1, Table1[sitename] )
    )
VAR catPrevious_ =
    CALCULATE (
        MAX ( Table1[category] ),
        Table1[firstoccurrence] = previous_,
        ALLEXCEPT ( Table1, Table1[sitename] )
    )
VAR catPrevious2Prev_ =
    CALCULATE (
        MAX ( Table1[category] ),
        Table1[firstoccurrence] = previous2Prev_,
        ALLEXCEPT ( Table1, Table1[sitename] )
    )
RETURN
    IF (
        Table1[category] = "Site Down",
        IF (
            catPrevious_ = "Low Voltage"
                && catPrevious2Prev_ = "AC Mains Failure",
            "Yes",
            "No"
        )
    )

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

 

  • ahsan005's avatar
    ahsan005
    Frequent Visitor
    3 years ago

    AlB thanks for your reply. it seems to work for small datasets but for the actual data (>50k+ rows) it crashed. However alxexis's answer seems to be the most comprehensive one!