How to efficiently fetch an earlier row that is unique through two attributes?

Question

Previously, I asked about how we can fetch a simple previous row through an incremented ID field (Thank you Petr Havlík). In this case I have ID and ACTIVITY, where (ACTIVITY&ID) is the unique value per row.

From an SQL perspective I just do an inner join where ACTIVITY = Joined ACTIVITY and ID = ID - 1 in the joined table and get the row I need.

In other words, I want the previous percentage belonging to the same activity.

So using the answer in the previous post I was able to get the result I want on 1000 rows. However if I were to increase this number of rows to 85000+ this function is dauntingly slow.

=SUMX(FILTER ( Query, (EARLIER ( [ID] ) = [ID] + 1)&&(EARLIER([ACTIVITY])=[ACTIVITY])),[PERCENTAGE])

My end result is to make this function on up to 7 million rows, if this is possible, how I can optimize it ? And if it isn't, could you explain to me why I can't do it ?

Fetching previous rows with the same ID and Activity

score 3 · Accepted Answer · answered Nov 18 '14 at 16:56

One option could be to try a variation on the approach - without your dataset I can't test whether it is more efficient but I've run similar things on 1m+ row datasets without issue:

=
CALCULATE (
    SUM ( [PERCENTAGE] ),
    FILTER (
        Query,
        [ID] = EARLIER ( [ID] ) - 1
        && [ACTIVITY] = EARLIER ( [ACTIVITY] )
    )
)

Probably not what you want to hear but doing this with SQL on import is probably your best bet.

score 2 · Answer 2 · answered Nov 18 '14 at 22:00

2

The best answer here would be using Lookupvalue, that would bypass any filters you need to do and allows you to do a direct lookup of values in the table. This would be much faster.

It would look something like:

=LOOKUPVALUE(table[PERCENTAGE], [ID] = EARLIER ( [ID] ) - 1)

Please make sure the ID values are unique as lookupvalue can only return a single result, when more than one rows are returned it will error out. You can potentially wrap it around with iserror

= IF(ISERROR(LOOKUPVALUE(table[PERCENTAGE], [ID] = EARLIER ( [ID] ) - 1)), BLANK()
           , LOOKUPVALUE(table[PERCENTAGE], [ID] = EARLIER ( [ID] ) - 1)
            )
     )

answered Nov 18 '14 at 22:00

Kasper de Jonge - MSFT

115
5

Kasper, great to see you on here! Absolutely bow to your superior knowledge here although in this case on a similarly sized data set both options are basically too quick to notice a substantive difference. That said my PC is a beast :-) Will be giving your technique a go in the future! – Jacob Nov 18 '14 at 23:03
Kasper, nice to see you here :-) – Petr Havlik Nov 19 '14 at 00:12

score 2 · Answer 3 · edited May 23 '17 at 12:26

JShmay,

this is pretty much the same question - and as Jacob has suggested, you can use logical operators that are normally available in Excel/PowerPivot.

You can really go crazy with this and should you need something more complex - for example to get difference between two points following some other condition, I would point you to very similar questions and my answers to them:

Hope this helps :)

How to efficiently fetch an earlier row that is unique through two attributes?

3 Answers3