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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
pbiforum123
Post Patron
Post Patron

Fuzzy lookup match

Table:1

Campaign Objective Campaign Code
Awareness AWA
Consideration CONSI
Video views VID
Store Traffic STORE
Reach REACH

 

Table:2

Campain Objective Code Output
AWA Awareness
AWA TraffA Awareness
CONSI O-2LH7G Consideration
AWA O-2VL9K Awareness
VID Video views
STORE Store Traffic
C experiment C experiment
NA NA
Awareness Awareness
Reach (1+) Reach
Followers Followers

 

If the value of column "Campaign Code" in table 1 exists in sub string of column "Campain Objective Code" in table 2, then do the lookup and get the mappping value based on the match from "Campaign Objective" column from Table 1 and put it in Output column of Table:2 as you see above. If the match is not found put the value as it is for example "C experiment" and "Followers"

 

Can someone please help on this?

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

Hi @pbiforum123 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Output = 
VAR _cobj =
    CALCULATE (
        MAX ( 'Table1'[Campaign Objective] ),
        FILTER (
            ALL ( 'Table1' ),
            IFERROR (
                SEARCH ( 'Table1'[Campaign Code], 'Table2'[Campaign Objective Code], 1, 0 ),
                0
            ) > 0
        )
    )
RETURN
    IF ( ISBLANK ( _cobj ), 'Table2'[Campaign Objective Code], _cobj )

vyiruanmsft_0-1682908514480.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.