DAX - IF Statement to Hold Last Value

[stalerik]

Hello,

I am trying to come up with a DAX formula for a calculated column "Last Index" that will return the current index if the NAME has changed from previous value, and hold the last index if the value has not changed.  Please see the example data below.  I have tried to make an if statement (IF current name <> previous name, index, __________)  but I'm getting stuck with the Else.  I've also tried LASTNONBLANK but I'm confused by it and getting errors.

Thanks for the help!

Example Data

Index	NAME	Last Index
1	A	1
2	A	1
3	B	3
4	B	3
5	C	5
6	C	5
7	C	5
8	A	8
9	A	8
10	C	10

[Anonymous]

Why can't you use a composite model so that you can mix DQ and Import?

[stalerik]

Since my source table was changed to Direct Query, I was thinking that it might not refresh correctly if I made the calculated table as Import.  I've usually only made models with one or the other, not a composite.  I will try a composite model like you suggest.  Thank you again for a quick response.

[Anonymous · Accepted solution]

[Last Index] = // calc column
var VeryFirstIndex = MIN( T[Index] )
var CurrIndex = T[Index]
var CurrName = T[Name]
var PrevIndex = CurrIndex - 1
var PrevName =
    MAXX(
        FILTER(
            T,
            T[Index] = PrevIndex
        ),
        T[Name]
    )
var Result =
    switch( true(),
    
        // If we're on the first row,
        // return the current index.
        CurrIndex = VeryFirstIndex, CurrIndex,
        
        // If the current name is same as
        // the prev name, return the value
        // of the index for the last different
        // name before this name plus 1.
        CurrName = PrevName,
            MAXX(
                FILTER(
                    T,
                    T[Name] <> CurrName
                    &&
                    T[Index] < CurrIndex
                ),
                T[Index]
            ) + VeryFirstIndex,
        
        // CurrName <> PrevName
        CurrIndex
    )
RETURN
    Result

[stalerik]

This calculated column works great for Import tables, but I have recently switched the data source to be Direct Query and this method does not work with the MAXX function.  Is there a method for performing the same calculations with a measure instead of a column, or some other way for Direct Query?  I have tried to modify it but I am getting stuck.

[stalerik]

This works great, thank you!