Forum Discussion
Lookupvalue for a Search String in Tabular Model
Hi seeking assistance with Tabular Model DAX query. Sorry for posting in PBI forum. I will be mgrating this solution to existng PBI, but the roadmap for it is 6-9 month away.
I've a Lookup/Reference table which provides the Sport Items and Sports related to it. Like
SPORT_ITEM SPORT
_____________________________
BaseballBAT Baseball
BASEBALL BAT Baseball
Baseball Glove Baseball
Helmet Football
Shoulderpads Football
Shoulder Pads FootballThen I have a table which has descriptive column. Like
ITEM_DESCRIPTION
__________________
Baseballbat Needed
Baseball Bat required
Helmet wanted
ShoulderPads provided
Shoulder Pads neededWhat I've been asked to do is - Lookup the value under ITEM_DESC and to the string matching SPORT_ITEM and return SPORT name column.
So I should see
ITEM_DESCRIPTION SPORT
__________________________________
Baseballbat Needed Baseball
Baseball Bat required Baseball
Helmet wanted Football
ShoulderPads provided Football
Shoulder Pads needed FootballNote:
- Unfortunately there is no relationship between the 2 tables. This lookup is the only way to join.
- I cannot do this join in the data source because I'm not allowed by the DBAs. Long story, (you didnt hear me say red tape).
- I have tried LOOKUPVALUE. Though, it returns expected value only when there is exact match to search string.
- I need to match on Substring.
- I tried using SEARCH along with LOOKUPVALUE, in 3rd argument. But I cannot because for SEARCH the lookup table is out of scope. it will only read from the table it is used in.
Since there is no join, I cannot use Related, right? And since this is Tabular Model, there is no CONTAINSVALUE.
Please let me know how this can be achieved.
Any help is much appreciated
For those who may stumble upon similar issue -
After a lot of reading everywhere, found the below link and especially in comments.
A combination of a various solutions in comments helped.
8 Replies
For those who may stumble upon similar issue -
After a lot of reading everywhere, found the below link and especially in comments.
A combination of a various solutions in comments helped.
Hi Anonymous ,
First of all, you would need the same strings (as Power BI is case sensitive) in both the columns. For this, you can go to the Query editor-> select SPORT_ITEM column from table 1 and UPPERCASE every letter.
Similarly, first go to the ITEM_DESCRIPTION column of second table and split it by a delimiter " " - Rightmost (space).
You would get a new column with just the SPORT item names, Rename that to ITEMS.Now UPPERCASE this column.
Close and Apply the changes.
Now, use your LOOKUPVALUE DAX on these columns:
new column in second table:
SPORT= LOOKUPVALUE( table_1(SPORT), table_1( SPORT_ITEM), table_2(ITEMS))This would create the new column with SPORTS name in table 2.
I hope this helps!
If I answer your question, mark it as a solution.
Thanks a lot Tanushree_Kapse for replying.
This doesnot work. Like I mentioned, I tried this, but it matches only where the match is EXACT. However I'm looking for matching a substring of the column ITEM_DESCRIPTION.
The ITEM_DESCRIPTION column can have other text in there. Like
Baseball needed Baseball Helmet wantedAlso this is Tabular Model. Though solution should be via DAX.
Anonymous , that's why we are CAPITALIZING every letter and splitting the column to get the substring seperated.
