Reply
joep78
Helper III
Helper III
Partially syndicated - Outbound

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

 

 

TypeSpaceOutcome
sensor 112AName A
sensor 112BName A
sensor 112Name B
Sensor 213CName C
Sensor 213Name D
Sensor 2 Name E

 

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Syndicated - Outbound

Hi, @joep78 

Please check the below.

it is for creating a new column.

 

Picture2.png

 

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

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Syndicated - Outbound

Hi, @joep78 

Please check the below.

it is for creating a new column.

 

Picture2.png

 

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

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Syndicated - Outbound

Hi Jihwan Kim,

 

Thanks for this, really helpful! 

 

Regards, Joep

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric Community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)