Forum Discussion
Advocate IICreating a Flag for Stable or Reduced Rating
Hi Community!
I have a table similar to the below:
| Client | Assessment | Rating | Flag |
| 123 | Initial | 2 | Stable or Reduced |
| 456 | Initial | 1 | Stable or Reduced |
| 123 | Final | 1 | Stable or Reduced |
| 456 | Final | 1 | Stable or Reduced |
| 789 | Initial | 3 | Not Stable or Reduced |
| 789 | Final | 4 | Not Stable or Reduced |
Basically, I am looking to create a column with a flag (similar to the last one above) that I can use as a filter, which indicates whether a client - from their Initial to Final Assessment - provided a stable or reduced rating. If their rating went up, then I'd like to exclude them from the flag.
I think the trouble I'm having with this is that the rating provided can be anywhere from 1 to 5.
Appreciate any and all advice on how to accomplish this either via a conditional column or a measure.
Thank you!
samdep Seems like you need to be able to compare the current row with the previous row, that's the MTBF pattern. Although your use case is entirely different. You will need an index column or date column to define "previous".
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
2 Replies
samdepGreg_Deckler - Thank you, this worked for my needs! Unfortunately, while I have a date field, it isn't reliable (out of order, etc.), but your index suggestion was extremely helpful. An outright addition of an index wouldn't necessarily work since there other assessments in between these and not necessarily ordered accurately. But, I was able to assign an "index" of sorts to the the two respective assessments - and from there, the MTBF pattern works perfectly. Thank you again!!
- Greg_Deckler
Community Champion
samdep Seems like you need to be able to compare the current row with the previous row, that's the MTBF pattern. Although your use case is entirely different. You will need an index column or date column to define "previous".
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous