Forum Discussion
Helper ITwo Calculated Columns with Similar LOOKUPVALUE Have Different Results
I created two calculated columns. Column 1 shows result = "Yes" and alternate result = "No". Column 2 with same set of conditions shows result = searched column and alternate result = "N/A". So if Column 1 = "Yes", Column 2 should = searched value, and if Column 1 = "No", Column 2 should = "N/A". But sometimes, in some rows, Column 1 = "Yes" and Column 2 = "N/A". Why?
Table1
| Col1A | Col1B | Col1C | Col1D |
| Green | Apple | Yes | Summer |
| Red | Strawberry | Yes | Fall |
Table2
| Col2A | Col2B | Col2C | Col2D |
| Red | Strawberry | Yes | Fall |
| Red | Apple | No | N/A |
| Green | Apple | Yes | N/A |
| Yellow | Pear | No | N/A |
Col2C = lookupvalue(Table1[Col1C], Table1[Col1A], Table2[Col2A], Table1[Col1B], Table2[Col2B], "No")
Col2D = lookupvalue(Table1[Col1D], Table1[Col1A], Table2[Col2A], Table1[Col1B], Table2[Col2B], "N/A")
Third row Col2D column is incorrect. Value should be Summer.
Hi, jabrillo
You can try the following methods.
Column2C = Var _N1=CALCULATE(MAX(Table1[Col1C]),FILTER(Table1,[Col1A]=EARLIER(Table2[Col2A])&&[Col1B]=EARLIER(Table2[Col2B]))) Return IF(_N1=BLANK(),"No","Yes")Column2D = Var _N2=CALCULATE(MAX(Table1[Col1D]),FILTER(Table1,[Col1A]=EARLIER(Table2[Col2A])&&[Col1B]=EARLIER(Table2[Col2B]))) Return IF(_N2=BLANK(),"N/A",_N2)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- parry2k
Super User
jabrillo it will be easier if you provide sample data and the expected output. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 
v-zhangtiCommunity Support
Hi, jabrillo
You can try the following methods.
Column2C = Var _N1=CALCULATE(MAX(Table1[Col1C]),FILTER(Table1,[Col1A]=EARLIER(Table2[Col2A])&&[Col1B]=EARLIER(Table2[Col2B]))) Return IF(_N1=BLANK(),"No","Yes")Column2D = Var _N2=CALCULATE(MAX(Table1[Col1D]),FILTER(Table1,[Col1A]=EARLIER(Table2[Col2A])&&[Col1B]=EARLIER(Table2[Col2B]))) Return IF(_N2=BLANK(),"N/A",_N2)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
