topic How to search a text column using a column list in another table and output first instance in DAX Commands and Tips

How to search a text column using a column list in another table and output first instance

rchamberlain — Fri, 08 Nov 2024 15:03:44 GMT

Hello -

This situation seems so simple, but I'm struggling to get it to work.

I have two seperate tables and need to search a column in Table 1 using a list of values from a column in Table 2.

Example:

In Table1 I have a column called "Tags" with the following values listed in each.

Table1[Tags]

Tags

condition-4; condition-4.2; condition-3

condition-9; condition-5; condition-10

condition-8; condition-7; condition-6

I'm trying to create a calculated column that searches this field using a column in Table2 like this.

Table2[Condition_Name]

Condition_Name

condition-10

condition-9.1

condition-9

condition-8

condition-7

condition-6

condition-5

condition-4.2

condition-4.1

condition-4

What I need the calculated column to do is search Table1 using the values in Table2 and output the first instance starting from Top to bottom.

Expected output for the new calculated column in Table1

Tags	New Calculated Column
condition-4; condition-4.2; condition-3	condition-4.2
condition-9; condition-5; condition-10	condition-10
condition-8; condition-7; condition-6	condition-8

I hope this makes sense and would be very greatful if someone can help!

Thanks,

Rich

Re: How to search a text column using a column list in another table and output first instance

lbendlin — Fri, 08 Nov 2024 17:49:46 GMT

and output the first instance starting from Top to bottom.

Power BI has no concept of that. You need to indicate what you mean by "top" or "bottom". Usually by adding an index column

Your request doesn't seem to depend on user input, so instead of doing it as a calculated column you could also decide to do it in Power Query.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PS8ksyczP0zWxVkDi6Bkhc42VYnWQ1VoiS5oicwwN0JRaIMuaI3PMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom Column", each List.Intersect({Text.Split([Tags],"; "),#"Condition_Name"[Condition_Name]})), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", (k)=> List.First(Table.Sort(Table.SelectRows(#"Condition_Name",each List.Contains(k[Custom Column],[Condition_Name])),"Index")[Condition_Name])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom Column"}) in #"Removed Columns"

Re: How to search a text column using a column list in another table and output first instance

DataInsights — Fri, 08 Nov 2024 17:51:01 GMT

rchamberlain,

Try this solution.

1. Create an Index column in Power Query for Table2 (Condition_Name).

2. Create calculated column in Table1 (Tags).

New Calculated Column = VAR vTag = Tags[Tags] // get the minimum index (multiple matches may occur) VAR vMinIndex = MINX ( FILTER ( Condition_Name, CONTAINSSTRING ( vTag, Condition_Name[Condition_Name] ) ), Condition_Name[Index] ) VAR vResult = // get the condition name corresponding to the minimum index MAXX ( FILTER ( Condition_Name, Condition_Name[Index] = vMinIndex ), Condition_Name[Condition_Name] ) RETURN vResult

Re: How to search a text column using a column list in another table and output first instance

rchamberlain — Fri, 08 Nov 2024 18:22:49 GMT

Thanks for the quick reply! This worked perfectly!