Forum Discussion

ngocnguyen's avatar
ngocnguyen
Helper IV
5 years ago
Solved

Convert word with specific text

Hi I have 2 tables as below   For column" Result" in Table1: Every row in column" Customer name"  that contain words mentioned in column" Ship to Party Name" in Table2  will be change into the tex...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, ngocnguyen 

     

    Try this:

     

    Measure =
VAR cname =
    MAX ( Table1[Customer name] )
RETURN
    MAXX (
        FILTER (
            Table2,
            FIND ( Table2[Snip-to Party Name], cname,, -1 ) > 0
                && LEN ( Table2[Snip-to Party Name] )
                    = IF (
                        LEN ( Table2[Snip-to Party Name] ) = LEN ( cname ),
                        LEN ( Table2[Snip-to Party Name] ),
                        MAXX (
                            SUMMARIZE (
                                Table2,
                                [Snip-to Party Name],
                                [Convert],
                                "length", LEN ( Table2[Snip-to Party Name] )
                            ),
                            [length]
                        )
                    )
        ),
        [Convert]
    )

     

    Best Regards

    Janey Guo

     

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

ngocnguyen's avatar
ngocnguyen
Helper IV
5 years ago

v-janeyg-msft 

Thanks for your support. I do as the same your instruction. However, for case "XNK", the convert result is still" Nguyen Kim" While the correct result should be  blank T_T

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, ngocnguyen 

 

Did you change the 'maxx' to 'minx' in the formula above?

 

Best regards

Janey Guo

  • ngocnguyen's avatar
    ngocnguyen
    Helper IV
    5 years ago

    v-janeyg-msft 

    Yes, I changed already but still not get the correct result. Could you pls send me your instance of power bi

     

    Measure =

    var cname = max(Table1[Customer name])
    return
    MinX (
    FILTER (
    Table2,
    FIND (Table2[Ship-to Party Name],cname,,-1) > 0
    ),
    Table2[Convert]
    )

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, ngocnguyen 

     

    Try this:

     

    Measure =
VAR cname =
    MAX ( Table1[Customer name] )
RETURN
    MAXX (
        FILTER (
            Table2,
            FIND ( Table2[Snip-to Party Name], cname,, -1 ) > 0
                && LEN ( Table2[Snip-to Party Name] )
                    = IF (
                        LEN ( Table2[Snip-to Party Name] ) = LEN ( cname ),
                        LEN ( Table2[Snip-to Party Name] ),
                        MAXX (
                            SUMMARIZE (
                                Table2,
                                [Snip-to Party Name],
                                [Convert],
                                "length", LEN ( Table2[Snip-to Party Name] )
                            ),
                            [length]
                        )
                    )
        ),
        [Convert]
    )

     

    Best Regards

    Janey Guo

     

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