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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
random_Bi_User
Frequent Visitor

Search All Column Values in a different Column

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!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @random_Bi_User 

please try

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

 

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @random_Bi_User 

please try

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

 

Thank you very much @tamerj1 it worked perfectly! 

SpartaBI
Community Champion
Community Champion

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

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-1laptopLaptop
SmatphonetestdataSmartphone
1-y.2,LaptopasLaptop

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors