Create a Measure which compares a measure with a static table of thresholds

[tharaujo85]

Hello!

I want to Create a Measure which compares a measure (PlanCob) with a static table of thresholds and returns a constant (5 i.ex.) on the selected date when it crosses one of the thresholds, as it follows:

[v-xinruzhu-msft · Accepted solution]

Hi @tharaujo85 

1.You can create a index measure first

Index = RANKX(ALLSELECTED('Table'),CALCULATE(MAX([dat_data])),,ASC))

 2.Then create the following measure

Measure = var _inde=[Index]
var a=MINX(FILTER(ALLSELECTED('Table'),[PlanCob]=SELECTEDVALUE('Table'[PlanCob])),[dat_data])
var b=MINX(FILTER(ALLSELECTED('Table (2)'),[thresholds]>SELECTEDVALUE('Table'[PlanCob])),[thresholds])
var c=MAXX(FILTER(ALLSELECTED('Table'),[Index]=_inde-1),[PlanCob])
var d=MINX(FILTER(ALLSELECTED('Table (2)'),[thresholds]>c),[thresholds])
return IF(SELECTEDVALUE('Table'[dat_data])=a&&b<>d&&ISBLANK(b)=FALSE(),5)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tharaujo85]

Just didn't understood why create the 'a' variable. I commented it and it worked ok. Do you see any case that it will be necessary?

[tharaujo85]

Thank you @v-xinruzhu-msft !!!

Very interesting manner to solve it. Took me sometime but understood it.

[amitchandak]

@tharaujo85 , If thresholds table has date, you can join both then on common date dim and pull the measure from both table together 

or Have a measure like

calculate(Sum(thresholds[Value]), filter(thresholds, thresholds [Date] = max(Table[Date]) ) )

You can also consider treatas

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[tharaujo85]

Hi @amitchandak ! Thanks for your reply.

It doesn't have dates....it's a simple table to mark when inventory crosses theses thresholds of monthly coverage.
I believe the measure has to read the PlanCob at the selected day and PlanCob the day before and check if the former is smaller than one of the thresholds and the latter is greater.