Forum Discussion
value based on specific characters
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.
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
2 Replies
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
Hi Jihwan Kim,
Thanks for this, really helpful!
Regards, Joep
