Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 😓
Table 1 | |
Project | Comments |
Berlin | Property already listed - M15 |
Berlin | M55 - Purchased in cash payment |
Hamborg | F11 - Property has decreased in value |
Hamborg | M55 - Purchased in cash payment |
Frankfurt | M55 + F11 - Property has decreased in value but transaction made as cash purchase |
Table 2 | ||
Comment group | Lookup text | Rank |
Listed property | M15 | 1 |
Depreciation | F11 | 2 |
Cash purchase | M55 | 3 |
Desired outcome in Table 1
Table 1 - Result | ||
Project | Comments | Comment group |
Berlin | Property already listed - M15 | Listed property |
Berlin | M55 - Purchased in cash payment | Cash purchase |
Hamborg | F11 - Property has decreased in value | Depreciation |
Hamborg | M55 - Purchased in cash payment | Cash purchase |
Frankfurt | M55 + F11 - Property has decreased in value but transaction made as cash purchase | Depreciation |
Solved! Go to Solution.
CalculatedColumn=MAXX(TOPN(1,FILTER(Table2,FIND(Table2[LookupText],Table1[Comments],1,0)),Table2[textRank],ASC),Table2[Comment group])
Comment Group =
MAXX(
TOPN(
1,
FILTER( Table2, CONTAINSSTRING( Table1[Comments], Table2[Lookup text] ) ),
Table2[Rank], ASC
),
Table2[Comment group]
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Comment Group =
MAXX(
TOPN(
1,
FILTER( Table2, CONTAINSSTRING( Table1[Comments], Table2[Lookup text] ) ),
Table2[Rank], ASC
),
Table2[Comment group]
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
CalculatedColumn=MAXX(TOPN(1,FILTER(Table2,FIND(Table2[LookupText],Table1[Comments],1,0)),Table2[textRank],ASC),Table2[Comment group])