cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ceeveeonline
Helper II
Helper II

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         Football

Then I have a table which has descriptive column. Like

ITEM_DESCRIPTION
__________________
Baseballbat Needed
Baseball Bat required
Helmet wanted
ShoulderPads provided
Shoulder Pads needed

What 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        Football

Note:

  • 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

1 ACCEPTED SOLUTION
ceeveeonline
Helper II
Helper II

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.

Solution Link 

View solution in original post

8 REPLIES 8
ceeveeonline
Helper II
Helper II

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.

Solution Link 

View solution in original post

Tanushree_Kapse
Resolver III
Resolver III

Hi @ceeveeonline ,

 

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 wanted

 

Also this is Tabular Model. Though solution should be via DAX.

@ceeveeonline , that's why we are CAPITALIZING every letter and splitting the column to get the substring seperated.

But splitting cannot be 100%. i.e. I can have spaces in between for the text I need to search.

base ball

Shoulder pad

@ceeveeonline , In that case: Use UPPER(Column_name) to convert every alphabet to uppercase.
And for splitting the column using delimiter for rightmost space-
RIGHT([ITEMS_DESCRIPTION],SEARCH(" ",[ITEMS_DESCRIPTION]),-1)

 

 

I hope this helps!

Thanks @Tanushree_Kapse  - I'll come back to this later. A priority 2 incident has happened. 😞

 

Any other thoughts in meanwhile are welcome.

@Tanushree_Kapse Hi I'm back on this.

 

Sorry I forgot to specify that it is not necessary that ITEM_DESCRIPTION will contain 1 part at the end that can be discarded. It can be like

Baseball bat wanted by Team AZ
BaseballBat needed TeamCA
Base ball
Team NY Helmet

It can be any weird combination.

Hence I am specifcally looking for a lookup on part of string.

 

Sorry for not being clear.

 

Any help is much appreciated.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors