Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
samdep
Advocate II
Advocate II

Creating a Flag for Stable or Reduced Rating

Hi Community!

 

I have a table similar to the below:

 

ClientAssessmentRatingFlag
123Initial2Stable or Reduced 
456Initial1Stable or Reduced 
123

Final

1Stable or Reduced
456Final1Stable or Reduced 
789Initial3Not Stable or Reduced
789Final4Not 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!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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/3395....
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
samdep
Advocate II
Advocate II

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

Greg_Deckler
Super User
Super User

@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/3395....
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors