Forum Discussion

samroth's avatar
samroth
Microsoft Employee
6 years ago

Create column based off substrings from different table

I have two tables. The first is my main table with a bunch of information on items. One of the columns is an "Area Path". The following is a made-up example with similar information to what can be...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

samroth - I did it this way (below). PBIX is attached below sig.

Column = 
    VAR __Table = 
        ADDCOLUMNS(
            ALL('Table (4)'[Root Path]),
            "Depth",LEN([Root Path]) - LEN(SUBSTITUTE([Root Path],"\","")),
            "Match",FIND([Root Path],[Area Path],,0)
        )
    VAR __BestDepth = MAXX(FILTER(__Table,[Match]>0),[Depth])
RETURN
    MAXX(FILTER(__Table,[Match]>0 && [Depth] = __BestDepth),[Root Path])

 

samroth's avatar
samroth
Microsoft Employee
6 years ago

Thanks Greg, where I am supposed to put this code to run in the pbix?

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

    Hi , samroth 

    As shown in the attached file provided by Greg_Deckler ,you just need to create a calculated column.

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