Return a value that has no match in LOOKUPVALUE (or some other function)

[JLambs20]

Hello!  I have 2 unrelated tables where I need to pull some information across into the second table.  Table 2 contains all "capabilities", but table one only has a few of those capabilities listed. Utilizing LOOKUPVALUE, I was able to match the available capabilities into table 2 from table 1. However, in Power BI, the cells that do not match naturally come back as blank.  That being said, I am trying to figure out how to return the "alternate value" from Table 1 (in this case, it is called "All Other").  This alternate value needs to be the value listed in the first table and not just free text.  To simplify, I need to have all non-matching values in table 2 return the unmatched value from table 1. If LOOKUPVALUE will not work here, I'm open to other suggestions! Here's a screenshot:


Thank you in advance!

[smpa01]

@Anonymous  if you have following two tables, respectively _t1 and _t2

CAT	Val
CAT1	One
CAT2	Two
CAT3	Three
CAT4	Others

Column1	Column
One	One
Two	Two
Three	Three
Four	Others
One	One
Two	Two
Three	Three
Five	Others
Six	Others
Seven	Others

You can achieve the desired result by this

Column = 
VAR _0 = MAXX (FILTER(_t1,_t1[Val]=EARLIER(_t2[Column1])),_t1[Val])
VAR _1 = IF(_0=BLANK(),MAXX (FILTER(_t1,_t1[Val]="Others"),_t1[Val]),_0)
RETURN _1

[JLambs20]

@smpa01 were you able to catch my earlier reply? It seems you are close on the solution, but not 100% there quite yet. Thanks!

[smpa01]

@JLambs20  Can you please create pbix and uplaod here through one drive or gdrive. It is hard to understand formt he screenshot itself.

[JLambs20]

Ok, I'm not seeing how I can attach a .pbix file here.  Is there some step I am not seeing?

[smpa01]

@JLambs20  share it through grdrive/1drive

[JLambs20]

Hopefully this works! Test BI File 

[smpa01 · Accepted solution]

@JLambs20  how does it look?

_newColumn = 
VAR _0 = MAXX(FILTER(Table_1,Table_1[Capability]=EARLIER(Table_2[Capability])),Table_1[Capability])
RETURN IF(_0=BLANK(),"All Other",_0)

[JLambs20]

Thank you for that but something is still off.  It's clearly on my end because it worked well for you.  One thing to note, however, is that the "other" option in the formula (i.e, if the value is not found in table 1), needs to return the other value from Table 1 and not be free text. For certain subsets of data, the unmatched values could be listed as "All", or "All Other", but it all depends on the data.  That's why I can't use just free text in the "IF" statement argument.  So anyway, here's what I'm seeing in my full dataset.   That column with the green highlights is a simple LOOKUPVALUE formula.  The green highlighted cells should say "All Other" because the values in those rows do not match anything from Table 1.  As we can see here, the column with the blue highlights is indicating which cells should say "All Other".  You can see that SOME are correct, but not all. 

Again, I appreciate the efforts here. 

[smpa01]

@JLambs20  not clear whay you are after. It is evident from the screenshot that my formula returns what you are after. I am not sure what else do you need.

Also, "One thing to note, however, is that the "other" option in the formula (i.e, if the value is not found in table 1), needs to return the other value from Table 1" is itself contracdictory - if 

t2 [Capability] not found in t1[Capability], needs to "other" value from t1????? I have no idea what it means. A value is no not found, hence the overwriting with "All Other". 

Clarify?

[JLambs20]

Hello and thank you for the information! Unfortunately your solution is showing me data where it isn't expected.  I exported the data table into Excel and hid some columns but it still shows what's going on.  For some more context, I used "Lab Sciences Lead Time" as Table #1 and "SA Capabilities" as Table #2.  So did I do something wrong here? The red cells should say "All Other"

Since the red cells do not match up to what's in Table 1, they should say "All Other" as opposed to the values present. 

Thanks again!

[Anonymous]

@JLambs20 Function LOOKUPVALUE has optinal parameter 'alternateResult':

https://dax.guide/lookupvalue/

[JLambs20]

right, but how do I pull in the "alternate result" value from the table 1 column and not just use free text? Free text won't work in the context of the dataset (this is just a small snippet of a much larger dataset).