DAX column to trim and find match in other table

[cdebackere]

I Have 2 table,

In first table there is a column 'shortname'

In the second table there is a column 'longname'

Most of the longnames start with a string matching a shortname

So I want to add a calculated dax column to the second table to list the matching (trimmed) longname.

WHat I do: I trim the longname at the end, and then look for a match in the other table.

THen I need to return the longest (least trimmed) match.

The code I have works, and I am able to get the number of matches, but I am unable to the get the actuall trimmed longname, matching the shortname.

I spent nearly 2 hours trying out a million variants, but it just doesn't work (various errors)

This is the code I have. And the problem (with some of my attemps commented) is in the RETURN part

name.short = 
VAR OriginalString = [name.long]
VAR OtherTable = 'studentset' 
VAR CheckMatch = 
    GENERATE(
        ADDCOLUMNS(
            GENERATESERIES(1,LEN(OriginalString)),
            "TrimmedString", LEFT(OriginalString, LEN(OriginalString) - [Value] + 1)
        ),
        FILTER('studentset', [TrimmedString] = [name.short])
    )
VAR LargestMatch = 
    TOPN(1,
        CheckMatch)//,[Value],ASC)
VAR n1 = SELECTCOLUMNS(LargestMatch,"sln",[TrimmedString])

RETURN
//OriginalString
CheckMatch[[Value]]
//DISTINCTCOUNT(CheckMatch)
//IF(COUNTROWS(n1) > 0,FIRSTNONBLANK(LargestMatch[[TrimmedString]],1), "")
//IF(COUNTROWS(LargestMatch) > 0, COUNTROWS(LargestMatch), 0)
//))))))))

So now I reach out to the community

The issue seems to be that whenever I refer to a table as defined in one of the VAR's, I cannot use or get hold of a column of that table. So, for instance, the code editor does not accept CheckMatch[TrimmedString] to designate a column in the 'RETRUN' section

Thx for helping

Christof

[cdebackere]

@techies 

It seems like I was close, but I fail to see/understand why I didn't make it to the end, because I did try SELECTCOLUMNS as well. I'm going to dig a little because I hate it when I don't understand my mistake.. because I feel like I might be locked in some general misconception.

The important thing is: it works, so a massive 'thank you'

C.

[techies]

welcome, i totally get it, happens to me all the time too 🙂

[techies · Accepted solution]

Hi @cdebackere please check this

name.short =

VAR OriginalString = [name.long]

VAR TrimmedTable =

    ADDCOLUMNS(

        GENERATESERIES(1, LEN(OriginalString)),

        "TrimmedString", LEFT(OriginalString, LEN(OriginalString) - [Value] + 1)

    )

VAR Matches =

    FILTER(

        ADDCOLUMNS(TrimmedTable, "IsMatch",

            LOOKUPVALUE('studentset'[name.short], 'studentset'[name.short], [TrimmedString])

        ),

        NOT ISBLANK([IsMatch])

    )

VAR BestMatch =

    TOPN(1, Matches, [Value], ASC)  

RETURN

    IF(

        COUNTROWS(BestMatch) > 0,

        SELECTCOLUMNS(BestMatch, "Result", [TrimmedString]),

        BLANK()

    )