Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
_Martin123
Frequent Visitor

Lookup string value from unrelated table in ranked order

Hi Community,

 

I need your support.

 

I have two unrelated tables, where Table 1 consists of comments made by sales reps, whereas table 2 consists of lookup texts used to group comments in table 1. It may happen, that comments made by sales reps contains multiple lookup texts from Table 2, and in these cases, we select the [comment group] with the lowest [Rank].

The goal is to create a new column in table 1, that provides [Comment group], when [lookup text] is present in [comments]. If multiple [lookup text]'s are available, then select the one with the lowest [rank]. 

I was trying the following formula: but it does not take [rank] into consideration 😓
 

CALCULATE(
   FIRSTNONBLANK('Table 2'[Comment group],0),
    FILTER('Table 2' , CONTAINSSTRING('Table 1'[Comments],'Table 2'[Lookup text])),
    FILTER('Table 2', MIN('Table 2'[Rank])))



Table 1 
ProjectComments
BerlinProperty already listed - M15
BerlinM55 - Purchased in cash payment
HamborgF11 - Property has decreased in value
HamborgM55 - Purchased in cash payment
FrankfurtM55 + F11 - Property has decreased in value but transaction made as cash purchase



Table 2  
Comment groupLookup textRank
Listed propertyM151
DepreciationF112
Cash purchaseM553

 

 

Desired outcome in Table 1

Table 1 - Result 
ProjectCommentsComment group
BerlinProperty already listed - M15Listed property
BerlinM55 - Purchased in cash paymentCash purchase
HamborgF11 - Property has decreased in valueDepreciation
HamborgM55 - Purchased in cash paymentCash purchase
FrankfurtM55 + F11 - Property has decreased in value but transaction made as cash purchaseDepreciation

 

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

CalculatedColumn=MAXX(TOPN(1,FILTER(Table2,FIND(Table2[LookupText],Table1[Comments],1,0)),Table2[textRank],ASC),Table2[Comment group])

View solution in original post

ThxAlot
Super User
Super User

Comment Group = 
MAXX(
    TOPN(
        1,
        FILTER( Table2, CONTAINSSTRING( Table1[Comments], Table2[Lookup text] ) ),
        Table2[Rank], ASC
    ),
    Table2[Comment group]
)

ThxAlot_0-1716336611169.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

2 REPLIES 2
ThxAlot
Super User
Super User

Comment Group = 
MAXX(
    TOPN(
        1,
        FILTER( Table2, CONTAINSSTRING( Table1[Comments], Table2[Lookup text] ) ),
        Table2[Rank], ASC
    ),
    Table2[Comment group]
)

ThxAlot_0-1716336611169.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



wdx223_Daniel
Super User
Super User

CalculatedColumn=MAXX(TOPN(1,FILTER(Table2,FIND(Table2[LookupText],Table1[Comments],1,0)),Table2[textRank],ASC),Table2[Comment group])

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors