Forum Discussion

_Martin123's avatar
_Martin123
Frequent Visitor
2 years ago
Solved

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

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

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

  • ThxAlot's avatar
    ThxAlot
    2 years ago
    Comment Group = 
MAXX(
    TOPN(
        1,
        FILTER( Table2, CONTAINSSTRING( Table1[Comments], Table2[Lookup text] ) ),
        Table2[Rank], ASC
    ),
    Table2[Comment group]
)

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

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

  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago
    Comment Group = 
MAXX(
    TOPN(
        1,
        FILTER( Table2, CONTAINSSTRING( Table1[Comments], Table2[Lookup text] ) ),
        Table2[Rank], ASC
    ),
    Table2[Comment group]
)