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.

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

Hi, ngocnguyen 

 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure to calculate the desired result.

Like this:

Measure = 
var cname = MAX(Table1[Customer name])
return
MAXX (
    FILTER (
        Table2,
        FIND ( Table2[Snip-to Party Name], cname,,-1) > 0
    ),
    Table2[Convert]
)

If it doesn’t solve your problem, please feel free to ask me.

 

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 

    Many thanks for your support. However, I still not find the suitable solution.
    Maybe my explaination is not clear that make you confusing.
    So, i make another file for your understanding. (p/s: I hightlight red for some words that appear in " Ship to  party name" column in Table 2)
    The "correct" column in Table 1 is that I wanna to get

    In excel, I can get the result by combining Lookup & Countif, but in Power pivot  or Power query, I'm stuck with this

    Pls support me in this case.Thanks !

     

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

      ngocnguyen 

       

      Create a measure in PBI is easy, Why don’t you try it?

       

      • ngocnguyen's avatar
        ngocnguyen
        Helper IV
        5 years ago

        v-janeyg-msft 

        Yep, measure is ok, but your  suggested measure not yet solved my problem. Could you please suggest me another measure ?