Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to 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)
@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
@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.
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
@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)
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.
@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?
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?
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!
@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).
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
83 | |
76 | |
64 |
User | Count |
---|---|
142 | |
112 | |
98 | |
98 | |
94 |