IF Based on Category then LOOKUPVALUE to get value and Fill Field by Remark of Value

[Anonymous]

HI @amitchandak and team

Really Need help here . 
below is my tables

My LOGIC

From my table1 , IF Pest is RAT ( or any kind ) i will check my finding value then lookup value on table2 based on pest and value to decide detail LEVEL and put this detail value to my Remark Column on table

Thanks
SE

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Please try the following formula to create columns.

• In Table2:

Max Value = IF(CONTAINSSTRING([VALUE],"<"), INT(SUBSTITUTE([VALUE],"<","")))

Min Value = IF(CONTAINSSTRING([VALUE],">"), INT(SUBSTITUTE([VALUE],">","")))

• In Table1:

Remark = 
var _flag1= CALCULATE(MIN('Table2'[VALUE]),FILTER('Table2',[SITE]=EARLIER('Table1'[SITE]) && [FINDING]<[Max Value] ))
var _flag2= CALCULATE(MAX('Table2'[VALUE]),FILTER('Table2',[SITE]=EARLIER('Table1'[SITE]) && [FINDING]>[Min Value] ))
var _final=IF(_flag1=BLANK(),_flag2,_flag1)
return IF([PEST] in VALUES(Table2[PEST]), LOOKUPVALUE('Table2'[LEVEL],[VALUE],_final),BLANK())

The final output is shown below:

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