Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
roncruiser
Helper V
Helper V

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

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

NameIDValue
T1_abca1
xyz_T1ab2
abc_T2_xyzc3

abc_T2a_xyz

d4
abc_T2b_xyze5
T2c_xyzf6
xyzT1_abca7
abc_T1a_xyzb8

 

Reference Table

ContainsAlternate_Name
T1Reference1Max
T1aReference_1Min
T2Reference2Max
T2aReference_2Min
T2bReference_2Nom
T2cReferene_None

 

Expected Main Table wNew Column added.

NameIDValueAlternate_Name
T1_abca1Reference1Max
xyz_T1ab2Reference_1Min
abc_T2_xyzc3Reference2Max

abc_T2a_xyz

d4Reference_2Min
abc_T2b_xyze5Reference_2Nom
T2c_xyzf6Referene_None
xyzT1_abca7Reference1Max
abc_T1a_xyzb8Reference_1Min


Thank you...

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @roncruiser 

Please refer to attached sample file with the proposed solution

1.png

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

View solution in original post

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1687319362638.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

7 REPLIES 7
roncruiser
Helper V
Helper V

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!

@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. 

@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.

@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
Helper V
Helper V

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

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1687319362638.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


tamerj1
Super User
Super User

Hi @roncruiser 

Please refer to attached sample file with the proposed solution

1.png

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors