Forum Discussion
Create a Measure which compares a measure with a static table of thresholds
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:
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
5 Replies
- amitchandak
Super User
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/
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.
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
Thank you Anonymous !!!
Very interesting manner to solve it. Took me sometime but understood it.
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?
