cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JLambs20
Helper I
Helper I

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

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:

LOOKUPVALUE.jpg
Thank you in advance!

1 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)

 

smpa01_0-1634229247534.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

12 REPLIES 12
smpa01
Super User
Super User

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

 

CATVal
CAT1One
CAT2Two
CAT3Three
CAT4Others

 

Column1Column
OneOne
TwoTwo
ThreeThree
FourOthers
OneOne
TwoTwo
ThreeThree
FiveOthers
SixOthers
SevenOthers

 

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

 

 

smpa01_0-1634052124005.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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

@JLambs20  share it through grdrive/1drive





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Hopefully this works! Test BI File 

@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)

 

smpa01_0-1634229247534.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

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. Lab Sci BI snapshot.png

Again, I appreciate the efforts here. 

@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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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? LOOKUPVALUE Dax Example.jpgThe red cells should say "All Other"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!

Marik
Solution Supplier
Solution Supplier

@JLambs20 Function LOOKUPVALUE has optinal parameter 'alternateResult':

https://dax.guide/lookupvalue/

 

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).

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.