cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors