topic Re: DAX column to trim and find match in other table in DAX Commands and Tips

DAX column to trim and find match in other table

cdebackere — Mon, 26 May 2025 16:39:57 GMT

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

Re: DAX column to trim and find match in other table

techies — Mon, 26 May 2025 17:35:50 GMT

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()

)

Re: DAX column to trim and find match in other table

cdebackere — Mon, 26 May 2025 18:08:07 GMT

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'

Re: DAX column to trim and find match in other table

techies — Mon, 26 May 2025 18:19:37 GMT

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