topic Creating a Flag for Stable or Reduced Rating in DAX Commands and Tips

Creating a Flag for Stable or Reduced Rating

samdep — Thu, 13 Jan 2022 21:24:59 GMT

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!

Re: Creating a Flag for Stable or Reduced Rating

Greg_Deckler — Thu, 13 Jan 2022 21:41:01 GMT

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

Re: Creating a Flag for Stable or Reduced Rating

samdep — Fri, 14 Jan 2022 16:14:08 GMT

Greg_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!!