topic DAX - IF Statement to Hold Last Value in DAX Commands and Tips

DAX - IF Statement to Hold Last Value

stalerik — Wed, 28 Apr 2021 21:35:21 GMT

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

Re: DAX - IF Statement to Hold Last Value

Anonymous — Thu, 29 Apr 2021 00:17:04 GMT

[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

Re: DAX - IF Statement to Hold Last Value

stalerik — Thu, 29 Apr 2021 12:21:09 GMT

This works great, thank you!

Re: DAX - IF Statement to Hold Last Value

stalerik — Mon, 10 May 2021 13:56:48 GMT

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.

Re: DAX - IF Statement to Hold Last Value

Anonymous — Mon, 10 May 2021 14:09:04 GMT

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

Re: DAX - IF Statement to Hold Last Value

stalerik — Mon, 10 May 2021 14:16:49 GMT

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.