Check if value is contained from another table column then add column

[roncruiser]

Hi,

With DAX, I would like to check if column values from Reference[Contains] are contained in the Main[Name] column then return the value from the Reference[Alternate_Name] column as a new column to the Main Table.

It gets tricky with T1 as there is also a T1a.

Main Table

Name	ID	Value
T1_abc	a	1
xyz_T1a	b	2
abc_T2_xyz	c	3
abc_T2a_xyz	d	4
abc_T2b_xyz	e	5
T2c_xyz	f	6
xyzT1_abc	a	7
abc_T1a_xyz	b	8

Reference Table

Contains	Alternate_Name
T1	Reference1Max
T1a	Reference_1Min
T2	Reference2Max
T2a	Reference_2Min
T2b	Reference_2Nom
T2c	Referene_None

Expected Main Table wNew Column added.

Name	ID	Value	Alternate_Name
T1_abc	a	1	Reference1Max
xyz_T1a	b	2	Reference_1Min
abc_T2_xyz	c	3	Reference2Max
abc_T2a_xyz	d	4	Reference_2Min
abc_T2b_xyz	e	5	Reference_2Nom
T2c_xyz	f	6	Referene_None
xyzT1_abc	a	7	Reference1Max
abc_T1a_xyz	b	8	Reference_1Min

Thank you...

[tamerj1 · Accepted solution]

Hi @roncruiser 

Please refer to attached sample file with the proposed solution

Alternate_Name = 
VAR String = Main[Name]
VAR Items =
    SUBSTITUTE ( String, "_", "|" )
VAR Length =
    COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
    GENERATESERIES ( 1, Length, 1 )
VAR T2 =
    SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 =
    FILTER ( 'Reference', 'Reference'[Contains] IN T2 )
RETURN
    MAXX ( T3, 'Reference'[Alternate_Name] )

[roncruiser]

Both of the solution posted here worked, but I did not choose them as a solution as I found something that worked well for my specific needs.  Thank You posters.  I appreciate your efforts. 

I actually looked at both solutions from this thread but neither of them really fit my specific application.  I am not sure how a solution was chosen.  I did not choose any solution.  Nonetheless both solutions posted here worked and met my criteria but I went with something else.

This is what I went with:
https://www.youtube.com/watch?v=aXgHVXHRvd8

It worked fantastic and with less power query scripting lines.

Thank You everyone!

[tamerj1]

@roncruiser 

You went with a Power Query solution while your question is posted in the DAX forum and it starts with "With DAX" 

Both answers fulfilled all the requirements stated in the question and both are eligible to be marked as acceptable solutions. 
I have no idea who marked my answer as acceptable solution and I find it strange that you did not even bather to respond to the people who spent time and effort to help you. What is even stranger that you have only responded when you discovered that one of the answers have been marked as acceptable solution only to complain about it. 
Marked solutions help other people searching solutions for similar problems and this is the goal. 

Thank you for sharing a power query solution and you can definitely mark it as acceptable solution. 

[roncruiser]

@tamerj1 

Yeah, I realize that now.  I intended to do it in DAX but found out through trial and error it wasnt the right fit for my application at the moment.  My initial thought was to do it with DAX, but ended up doing it in Power Query.  As it turned out to have power query do most of the heavy lifting.

My intent was not to come across as complaining.  I was merely pointing out.  I did give a "Thank You" and truly meant it.  It was not token.  I've received much help within this community and appreciate the effort.  Maybe you overlooked the "Thank You"?  I don't know.

I also pointed out the solutions provided in this thread actually worked but not for my application.

There's your perception and there's intent.  My intent was not to complain but I cannot control your perception.  Just know my intent as I stated.

Still, it's a mystery as to why there are now two solutions as accepted.  I thought only the createor of the thread can accept solutions.  Again, I am not complaining.

[tamerj1]

@roncruiser 

I marked the other solution as acceptable solution because it is correct and can help others. 
Not only the author, but also community support and super users can mark solutions as acceptable. 

[devanshi]

Checking string = 
VAR containName = Main[Name]
RETURN
  MAXX(
              FILTER( Reference, CONTAINSSTRING(containName, Reference[Name])
                         )
                       Reference[Alternate_Name]             
)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Alternate_Name CC =
VAR _three =
    SUMMARIZE (
        GENERATE (
            FILTER ( Reference, LEN ( Reference[Contains] ) = 3 ),
            FILTER ( Main, CONTAINSSTRING ( Main[Name], Reference[Contains] ) )
        ),
        Reference[Contains],
        Reference[Alternate_Name],
        Main[Name],
        Main[ID],
        Main[Value]
    )
VAR _threereference =
    SUMMARIZE ( _three, Main[Name], Main[ID], Main[Value] )
VAR _two =
    GENERATE (
        FILTER ( Reference, LEN ( Reference[Contains] ) = 2 ),
        EXCEPT (
            SUMMARIZE (
                FILTER ( Main, CONTAINSSTRING ( Main[Name], Reference[Contains] ) ),
                Main[Name],
                Main[ID],
                Main[Value]
            ),
            _threereference
        )
    )
RETURN
    SUMMARIZE (
        FILTER ( UNION ( _three, _two ), Main[Name] = EARLIER ( Main[Name] ) ),
        Reference[Alternate_Name]
    )

[tamerj1 · Accepted solution]

Hi @roncruiser 

Please refer to attached sample file with the proposed solution

Alternate_Name = 
VAR String = Main[Name]
VAR Items =
    SUBSTITUTE ( String, "_", "|" )
VAR Length =
    COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
    GENERATESERIES ( 1, Length, 1 )
VAR T2 =
    SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 =
    FILTER ( 'Reference', 'Reference'[Contains] IN T2 )
RETURN
    MAXX ( T3, 'Reference'[Alternate_Name] )