value based on specific characters

[joep78]

Hi all,

I'm looking for a DAX query that will fill in the column 'Outcome' as follow:

If Type is "Sensor 1" AND Space Contains "A", OR " B" OR "C"  then NAME A

If Type is "Sensor 1" AND Space does not Contains "A", OR " B" OR "C"  then NAME B

If Type is "Sensor 2" AND Space does Contains "A", OR " B" then NAME C

If Type is "Sensor 2" AND Space does not Contains "A", OR " B" then NAME D

If Type is "Sensor 2" AND Space is emtpy  then NAME E

Type	Space	Outcome
sensor 1	12A	Name A
sensor 1	12B	Name A
sensor 1	12	Name B
Sensor 2	13C	Name C
Sensor 2	13	Name D
Sensor 2		Name E

I was not able to achive this, hopefully anybody can help me out! Thanks in advance.

[Jihwan_Kim · Accepted solution]

Hi, @joep78 

Please check the below.

it is for creating a new column.

Outcome CC =
SWITCH (
TRUE (),
'Table'[Type] = "sensor 1"
&& OR (
CONTAINSSTRING ( 'Table'[Space], "A" ),
CONTAINSSTRING ( 'Table'[Space], "B" )
), "NAME A",
'Table'[Type] = "Sensor 1"
&& NOT CONTAINSSTRING ( 'Table'[Space], "A" )
&& NOT CONTAINSSTRING ( 'Table'[Space], "B" )
&& NOT CONTAINSSTRING ( 'Table'[Space], "C" ), "NAME B",
'Table'[Type] = "Sensor 2"
&& OR (
CONTAINSSTRING ( 'Table'[Space], "A" ),
OR (
CONTAINSSTRING ( 'Table'[Space], "C" ),
CONTAINSSTRING ( 'Table'[Space], "B" )
)
), "NAME C",
'Table'[Type] = "Sensor 2"
&& NOT CONTAINSSTRING ( 'Table'[Space], "A" )
&& NOT CONTAINSSTRING ( 'Table'[Space], "B" )
&& 'Table'[Space] <> BLANK (), "NAME D",
'Table'[Type] = "Sensor 2"
&& 'Table'[Space] = BLANK (), "Name E"
)

https://www.dropbox.com/s/qkx7v34mo53yabf/joep.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[joep78]

Hi Jihwan Kim,

Thanks for this, really helpful! 

Regards, Joep