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:

 

FreemanZ's avatar
FreemanZ
Super User
3 years ago

Two additional comments:

1) Power BI is efficient handling columns but not that good handling rows. 

2) EARLIER is to get the row context in the earlier set of iteration, not the context of the earlier row or the earlier iteration scanning in the current set of iteration.