Using EARLIER() with a SMART INDEX.

Hi Nathaniel_C ,

That is a very neat post and it gave me some inspiration on how to use a smart index as a tool to use an index based on the sort order for multiple columns. Thanks for sharing!

I wanted to show an alternative to this without using PowerQuery but using DAX. The Smart Index column is then not a continous column but ranks all input based on the Time column, per Meter and Feeder. Also, I've used DAX variables rather then EARLIER() for performance. So I added two calculated columns, this is SmartIndexWithRANKX;

SmartIndexWithRANKX = 
VAR curMeter = Meter[Meter]
VAR curFeeder = Meter[Feeder]
RETURN
RANKX(FILTER(ALL(Meter), Meter[Feeder] = curFeeder && Meter[Meter] = curMeter), Meter[Time], , ASC)

The second calculated column is to calculate the difference with the previous reading:

HourlyDif Based on Rank = 
VAR curFeeder = Meter[Feeder]
VAR curMeter = Meter[Meter]
VAR prevSmartIndexRanked = Meter[SmartIndexRank] -1
RETURN
IF(prevSmartIndexRanked = 0, 
    BLANK(), 
    Meter[Hourly Reading] - CALCULATE(
        SELECTEDVALUE(Meter[Hourly Reading]), 
        FILTER(ALL(Meter), Meter[Feeder] = curFeeder && Meter[Meter] = curMeter && Meter[SmartIndexRank] = prevSmartIndexRanked))
)

Same result, just a different approach 🙂

What are your thoughts on this? I can imagine that on very large datasets, your method might be preferred as to push all calculations to the preperation of the data.

Either way, nice post and I will keep an eye out for using SmartIndexes 🙂

Kind regards,
Djerro123