Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Anonymous ,
Please try the following formula to create columns.
Max Value = IF(CONTAINSSTRING([VALUE],"<"), INT(SUBSTITUTE([VALUE],"<","")))
Min Value = IF(CONTAINSSTRING([VALUE],">"), INT(SUBSTITUTE([VALUE],">","")))
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.
Hi @Anonymous ,
Please try the following formula to create columns.
Max Value = IF(CONTAINSSTRING([VALUE],"<"), INT(SUBSTITUTE([VALUE],"<","")))
Min Value = IF(CONTAINSSTRING([VALUE],">"), INT(SUBSTITUTE([VALUE],">","")))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |