Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ahsan005
Frequent Visitor

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 check for Table[category row - 1] = "low voltage" && Table[category row - 2] = "AC Mains Failure", "YES" otherwise "NO"

 

I am unable to access the previous rows in DAX. I've tried using the EARLIER function but am unable to get the exact thing. This is easy to achieve in SQL using LAG() or NEIGHBOR() function.

 

Sample data link is shared below. Please note that this data is first sorted using 'sitename; and then by 'first occurence' column since it is always expected that low voltage & ac mains failure will come before site down respectively.

 

https://docs.google.com/spreadsheets/d/1nFIkjy_RBFW5VFGLbZND9AOBRYhLJfjP/edit?usp=share_link&ouid=10... 

 

Thanks!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

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

 

@AlexisOlson Yes It works. Thanks a lot!

Could you please explain the use of MAXX? Could it be any other iterator?

OFFSET returns a row and I want a specific column from that row, so I selected it using MAXX. There isn't anything special about MAXX; you could use other iterators too. SELECTCOLUMNS would work too and is probably more intuitive.

FreemanZ
Super User
Super User

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_0-1672042073353.png

 

@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?

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. 

AlB
Super User
Super User

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"
        )
    )

 

SU18_powerbi_badge

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
Frequent Visitor

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors