cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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:

 

IndexAttribute
1A
2A
3B
4B
5C

 

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.

 

IndexAttributeChanged
1A1
2A0
3B1
4B1
5C1
6C0

 

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!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@biz_wiz 

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

 

View solution in original post

Solution Sage
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 )
RETURN
previousValue = currentValue
 
The 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 results
 
 changed.png
 
Thanks for posting, it was a fun problem.
 
Every time I answer a question I learn something




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

2 REPLIES 2
Solution Sage
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 )
RETURN
previousValue = currentValue
 
The 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 results
 
 changed.png
 
Thanks for posting, it was a fun problem.
 
Every time I answer a question I learn something




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

Anonymous
Not applicable

@biz_wiz 

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

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors