DAX Help: Find partial match in another column to create a new column

[chadnelson]

I have two columns that look like the first two columns below (Ticket Creator & Group Members). I would like to create a third column (Member?) based on whether the ticket creator is a member of the group. Occasionally that group contains more than one member, and is never an exact match because of the affiliation listed in parenthesis.

[Ahmedx]

see my video

(x)=> if List.Count(List.Select({x[Group Members]}, (y)=>List.ContainsAny({y},{x[Ticket Creator]},Text.Contains )))=1 then "Member" else "Non-Member"

https://1drv.ms/v/s!AiUZ0Ws7G26Rix_XtJ3wJZUjAwEg?e=MwvH8E

[JTBusinessIntel]

Hey Chad, 

Try creating a custom column with this:

Membership = 

IF(CONTAINSSTRING([Ticket Creator], [Group Members]), "Member", "Non-Member")

As long as the name formatting is consistent between the two columns you should be alright to use this. 

Let me know if you have any questions!

Best,

JTB

[chadnelson]

@JTBusinessIntel  I have gotten the DAX to work for all instances except for those rows that have multiple members of the group. Those are all being labeled as non-member.

[JTBusinessIntel]

Chad, 

I apologize, the first DAX snippet I gave you is wrong. I sent it off the top of my head and flipped the arguments around. 

CONTAINSSTRING( ) works like this (text you want to search, text you want to find).

I put your fields in backwards, which is why it's not finding the values with multiple people, since that string is made of multiple members. 

Try flipping the arguments around inside the parentheses. 

IF(CONTAINSSTRING([Group Members], [Ticket Creator), "Member", "Non-Member")

Again, sorry for the confusion. 

Thanks, 
JTB

[chadnelson]

JTBusinessIntell  This helped, but maybe I was not very clear in my question. Using my example above, "Mouse, Micky" is being labeled as a "Member" but another row that includes "McQueen, Lightning" as the ticket creator and the same Group Members, still lists them as a "Non-Member". Myappologiess for not making that clear from the start.

[JTBusinessIntel]

Chad, 

As long as your substring matches the same spelling and format as the search value, you should be returning a match for the value in the ticket creator column. 

I'm assuming that there is another row that  is set up for McQueen, Lightning that's not returning a "member" result?

Is the formatting between the two columns the same? Is his name "McQueen, Lightning" in both?

The only thing I can think of is that potentially the name in the Ticket Creator <> the name in the Group Members, and as such, the substring is not recognized when the expression evaluates those records. 

[chadnelson]

Yes, I have another row where "McQueen, Lightning" is listed as the Ticket Creator and the Group Members column still lists members in the same order as the row where "Mouse, Mickey" is the Ticket Creator. Spelling is exactly the same for all names in both columns, but text/value is not exactly the same because the Group Members column also lists the group member's institutional affiliation inside parenthesis.

[JTBusinessIntel]

Chad,

As long as the spelling is the same, I cannot think of why the value would not return for the second member of the group. 

The only last thing I can think might work is possibly passing wildcards into the search value. I've not done this in application and don't have a good data source to test it for your use case, but your code would ideally look something like this:

Membership = 
var search_value = "*" & [Ticket Creator] & "*"
var return_value = IF(CONTAINSSTRING([Group Members], search_value ), "Member", "Non-Member")

return
return_value

hopefully the inclusion of the wildcards works with a dynamic list of members in the Ticket Creator column. 

I hope that works!

[chadnelson]

@JTBusinessIntel  Unfortunately no luck. Still only listing "Mouse, Mickey" as a Member and "McQueen, Lightning" as a non-member.

[chadnelson]

Unfortunately I am getting the following expression error.

"Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly."

[JTBusinessIntel]

Hey Chad, 

Are you attempting to put that code into your Power Query steps?

That is in DAX, not M. You'll need to create a column after load to use it. 

The M Equivalent of that code is:

if Text.Contains([Group Members],[Ticket Creator]) then "Member" else "Non-Member"

[chadnelson]

@AhmedxI really appreciate your help earlier. Any suggestions for this one?

[Ahmedx · Accepted solution]

and try this cod in DAX

https://1drv.ms/u/s!AiUZ0Ws7G26RiyB2oPICD1_E0FN4?e=xavMa7

[chadnelson]

@AhmedxThis worked! Thank you, again, so much! Have a good day.

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYwxCgMhEAC/slgp7A+uDUeKGBIsUsgVy7FcRFFYzyT+/iRNSDkwM94rW1plBBvWyF3hP4M+hZq5mwnsem/MGeEStueeQ95A38KHxKgFvZqbdITrqMbjB6AtyYuTmb6WK6kgnCkPycX+phRZxrJFBu12EniQ1HFcDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket Creator" = _t, #"Group Members" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket Creator", type text}, {"Group Members", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",  
   (x)=> if List.Count(List.Select({x[Group Members]}, (y)=>List.ContainsAny({y},{x[Ticket Creator]},Text.Contains )))=1 then "Member" else "Non-Member"
)
in
    #"Added Custom"