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

     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi ahsan005 

you would need to 
1) add an index column in Power Query

https://learn.microsoft.com/en-us/power-query/add-index-column

2) add a new column with DAX like this:

 

Tag = 
VAR _site = [sitename] 
VAR _index = [index]
VAR _table =
FILTER(data, [sitename]= _site)
VAR _lastcategory = 
MINX(
    FILTER( _table, [Index] =_index -1
    ),
    [category]
)
VAR _llastcategory =
MINX(
    FILTER( _table, [Index] =_index -2
    ),
    [category]
)
RETURN
IF(
    [category]="Site Down"
        &&_lastcategory="Low Voltage"
        &&_llastcategory="AC Mains Failure",
    "Yes", "No"
)

 

 

i tried and it worked like this:

 

ahsan005's avatar
ahsan005
Frequent Visitor
3 years ago

FreemanZ thanks for your reply. Your solution seems to work when I add the index using the sitename & category column, however with all the other columns mentioned in the sample data file it is giving 'No' for all the rows. Could we specify a column when trying to add an index column?