Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Solved! Go to Solution.
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.
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 wanted
Also 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.
But splitting cannot be 100%. i.e. I can have spaces in between for the text I need to search.
base ball
Shoulder pad
@Anonymous , 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.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |