topic Re: Search All Column Values in a different Column in DAX Commands and Tips

Search All Column Values in a different Column

random_Bi_User — Fri, 22 Jul 2022 08:40:36 GMT

Hi All!

I have a column with dirty data - lets call it ddColumn. Some of the Rows in the ddColumn contain product groups.
I would like to have a calculated column with the name of the product group - if found - and if not, just a blank value.
The product groups can be found in a product details table. The table with ddColumn has no relation to the product details table.

In a simplified way I thought it could look something like this:
if([productgroup] found in [ddColumn], return this [product group], "")
In dax i thought it could look something like this. But since a Column cant be used for search I do only get error.
If(Search('productdetails'[productGroup], [ddColumn], 1, Blank())>0, 'productdetails'[productGroup], Blank())

I hope someone of you might have a good solution for this.
Thanks in advance!

Re: Search All Column Values in a different Column

SpartaBI — Fri, 22 Jul 2022 10:32:13 GMT

random_Bi_User can you share sample tables so I could copy them to PBIX?

Re: Search All Column Values in a different Column

random_Bi_User — Fri, 22 Jul 2022 10:58:03 GMT

Thanks alot SpartaBI for your answer.

Is this enough or do you need more data?

Table1:
ddColumn

abcdSmartphone_content

content2022share

.1-1laptop

Smatphonetestdata

1-y.2,laptopas

product details table:
product group

Smartphone

Tablet

Laptop

Camera

desired output:
Table1:

ddColumn Calculated Column

abcdSmartphone_content	Smartphone
content2022share
.1-1laptop	Laptop
Smatphonetestdata	Smartphone
1-y.2,Laptopas	Laptop

Re: Search All Column Values in a different Column

tamerj1 — Fri, 22 Jul 2022 11:11:10 GMT

Hi random_Bi_User

please try

Calculated Column = MAXX ( FILTER ( VALUES ( Products[Group] ), CONTAINSSTRING ( Table1[ddcolumn], Products[Group] ) ), Products[Group] )

Re: Search All Column Values in a different Column

random_Bi_User — Fri, 22 Jul 2022 13:25:13 GMT

Thank you very much tamerj1 it worked perfectly!