Forum Discussion
Detect changed value in a column compared to previous row
Hi,
I need to filter my visual to only show rows where there has been a change in "Attribute" compared to the previous row.
We start with our dataset:
| Index | Attribute |
| 1 | A |
| 2 | A |
| 3 | B |
| 4 | B |
| 5 | C |
I imagine the solution would be to build a calculated column with DAX called "Changed". Value 1 for all rows where the there has been a change compared to the previous row, 0 when there has been no change.
| Index | Attribute | Changed |
| 1 | A | 1 |
| 2 | A | 0 |
| 3 | B | 1 |
| 4 | B | 1 |
| 5 | C | 1 |
| 6 | C | 0 |
Row 1 would ideally also have value 1 as it's the base value and I would like to include in the the visual.
Could somebody point me to the right direction for building th calculated column?
Thank you!
Anonymous
Column = VAR _prevattr = CALCULATE(MAX('Table'[Attribute]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index]))) RETURN IF('Table'[Attribute]<>_prevattr,1,0)
Please use this formula to track status change. However one thing I didn't get for index 4 changed value column value is 1 I think it should be 0. Let me know if you have questionThanks for providing a dataset that could be copied and pasted.
Here's the measure I wrote
Changed =VAR currentIndex =MAX ( Attributes[Index] )VAR previousIndex =IF ( currentIndex = 1, 1, CurrentIndex - 1 )VAR currentValue =MAX ( Attributes[Attribute] )VAR previousValue =CALCULATE ( MAX ( Attributes[Attribute] ), Attributes[Index] = previousIndex )RETURNpreviousValue = currentValueThe trick to get "previous" values in DAX is to use an idex (which you already had). There is a function named EARLIER, but it does NOT mean the previous row. It refers to the outer filter context and is not appropriate here.
This is a measure, so in several places you cannot just refer to a column directly, but have to wrap your reference in MAX(). The measure will run in a row context, where there will only be one value available. So MAX will return that value.
Here are my resultsThanks for posting, it was a fun problem.Every time I answer a question I learn something
2 Replies
- kentyler
Solution Sage
Thanks for providing a dataset that could be copied and pasted.
Here's the measure I wrote
Changed =VAR currentIndex =MAX ( Attributes[Index] )VAR previousIndex =IF ( currentIndex = 1, 1, CurrentIndex - 1 )VAR currentValue =MAX ( Attributes[Attribute] )VAR previousValue =CALCULATE ( MAX ( Attributes[Attribute] ), Attributes[Index] = previousIndex )RETURNpreviousValue = currentValueThe trick to get "previous" values in DAX is to use an idex (which you already had). There is a function named EARLIER, but it does NOT mean the previous row. It refers to the outer filter context and is not appropriate here.
This is a measure, so in several places you cannot just refer to a column directly, but have to wrap your reference in MAX(). The measure will run in a row context, where there will only be one value available. So MAX will return that value.
Here are my resultsThanks for posting, it was a fun problem.Every time I answer a question I learn something Anonymous
Column = VAR _prevattr = CALCULATE(MAX('Table'[Attribute]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index]))) RETURN IF('Table'[Attribute]<>_prevattr,1,0)
Please use this formula to track status change. However one thing I didn't get for index 4 changed value column value is 1 I think it should be 0. Let me know if you have question