Custom column: if a text contains a certain value, then return A

[hidenseek9]

Hello Power BI community,

I have a question regarding a custom column.

In my data set, I have a column with different brand names.

(Nike, Addidas, Reebok, etc.)

In the same dataset, I have another column with sports name.

(Soccer, Basketball, Tennis, etc.)

I would like to create a custom column that will give me

the brand names, but for just Nike,

if the sports is basketball I would like Power BI to return the value, "Nike Basketball".

How can I set up a custom column to get what I want?

Many thanks,

H

[BeemsC]

You can set up a custom column using the IF and the CONCATENATE functions, so:

Column = IF(table[column] = "Nike" ; CONCATENATE(text1;text2;"")

[BeemsC]

You can also add a space between by using this:

Column = IF(table[column] = "Nike" & " " ; CONCATENATE(text1;text2;"")

Or something like that ^^

[hidenseek9]

Thank you @BeemsC for a quick response!

That would work in this case
However, I was actually looking for
a different solution.

The reason is that I have another column with description.
If in the description, it contains a word [basketball]
then I also would like to create a custom column
that says Nike Basketball.

Could your help me with this?

Many thanks,

H

[BeemsC]

@hidenseek9 , Sorry i don't really understand.
Could you elaborate why my solution isn't working, and what it is exactly that you want ?

[hidenseek9]

@BeemsC

Under "Description", there is a sentence, not just a word.

This makes it a bit tricky for me.

For instance, if the brand is "NIKE" and a description reads "Shoes 28cm (Basketball) Male Size"

then I would want a custom column to say "NIKE Basketball"

Hope that is clear.

Appreciate your help!

H

[Ashish_Mathur]

Hi,

Try this

1. Assume a two column table with Brand in one column and Description in another.  This table is named Data

2. There is another table with only one column named eligible_sports

3. Take both these tables to the Data model and ensure that there is no relationship between the two

4. Write this calculated column formula in the Data Table

=FIRSTNONBLANK(FILTER(VALUES(eligible_sports[Eligible Sports]),SEARCH(eligible_sports[Eligible Sports],Data[Description],1,0)),1)

5. You may now concatenate in another column

Hope this helps.

[hidenseek9]

@Ashish_Mathur

Thank you for your input, but I am not quite sure if this expression works.

Perhaps my PowerBI DAX knowledge is not up to the level that

understands this expression.

DummyData

Please access the dummy data from above link and

let me know what expression would work (Or a custom column query)

Many thanks,

H

[Ashish_Mathur · Accepted solution]

Hi,

It works well.  The formula in the Sport column is

=FIRSTNONBLANK(FILTER(VALUES(eligible_sports[Eligible Sports]),SEARCH(eligible_sports[Eligible Sports],Data[Description],1,0)),1)

The formula in the next column is

=if([Brand]="Nike",[Brand]&" "&[Sport],BLANK())

[hidenseek9]

@Ashish_Mathur

It worked indeed!

Many thanks!

H

[Ashish_Mathur]

You are welcone.