Iteration for DAX CONTAIN Function inside an IF statement

[nikkollai]

Hello,

Another conundrum here i am having trouble to resolve.

I have two columns with data : Product ONE and Product TWO.
Some of the Product ONE data contains the word Modified ; i need to create an IF statement , find those records that contain the word Modified in column Product ONE and bring Product TWO value into a third column (The code i Need). If there is no word "Modified" then keep Product ONE valuein column The code i Need

You can see the end result below.
Any ideas will be highly appreciated.

Tks, N


[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ONE
[/TD]
[TD]Product TWO[/TD]
[TD]The code i Need
[/TD]
[/TR]
[TR]
[TD]PPA Modified
[/TD]
[TD]WOLAP-51-813
[/TD]
[TD]WOLAP-51-813
[/TD]
[/TR]
[TR]
[TD]PPA Modified
[/TD]
[TD]WOOA-65-8496
[/TD]
[TD]WOOA-65-8496
[/TD]
[/TR]
[TR]
[TD]AP05-12053
[/TD]
[TD][/TD]
[TD]AP05-12053
[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

You aren't looking for the CONTAINS() function, which is to look for a value anywhere in a column. You are looking for SEARCH() or FIND() though I can never keep them straight.

But it's just going to be a calc column with something like:
=IF( SEARCH("Modified", Table1[ProdOne], 1, 0), [ProdOne], [Prod2])

 

[nikkollai]

Thank you scottsen
It works. I actually tried this option but never put in the optional parameters start_num and NotFoundValueand was getting an error.
Any ideas why optional parameters suddenly became mandatory.

YIELDS AN ERROR =IF( SEARCH("Modified", Table1[ProdOne]), [ProdOne], [Prod2])
WORKS =IF( SEARCH("Modified", Table1[ProdOne], 1, 0), [ProdOne], [Prod2])

<tbody>
</tbody>

Thanks again
N -

 

[scottsen]

I honestly have an epic fight w/ those functions each time I use them Don't feel alone...

 

[nikkollai]

I honestly have an epic fight w/ those functions each time I use them Don't feel alone...

Alrighty thank you !