ContainsString with a MUST Have

DAX DAX Calculations
1

I want to create new column using DAX with a TRUE output. So i am using “ContainsString” function.

What I’m not able to do it align the DAX statement such that the first function is a must have in the string.
The measure i am trying to write should provide TRUE when Office is in the column but EMS is NOT there.

The business need is that all accounts should have both Office and EMS. I need to identify accounts that Have Office and NOT EMS. And what accounts have EMS and NOT Office
EDNA - ContainsString.xlsx (16.7 KB)
EDNA - ContainsString.pbix (17.8 KB)

2

Hi @chad.sharpe,

If you are looking to add a column, you can achieve that in Power Query.
Copy this script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk9Ly0xO1VFIzS3WUSjIL08tSspUitVBSGATK8sszswHi4C1QYTBfLAMqiawEqiOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Test Column" = _t]),
    AddOwoE = Table.AddColumn(Source, "Office w/o EMS", each Text.Contains([Test Column], "office", Comparer.OrdinalIgnoreCase) and not Text.Contains([Test Column], "ems", Comparer.OrdinalIgnoreCase), type logical),
    AddEwoO = Table.AddColumn(AddOwoE, "EMS w/o Office", each Text.Contains([Test Column], "ems", Comparer.OrdinalIgnoreCase) and not Text.Contains([Test Column], "office", Comparer.OrdinalIgnoreCase), type logical )
in
    AddEwoO

But a Calculated column is possible as well
For example:

Office wo EMS (CC) =
VAR String = SUBSTITUTE( 'Sample'[Test Column], ", ", "|")
VAR wOffice = PATHCONTAINS( String, "office")
VAR wEMS = PATHCONTAINS( String, "EMS" )
VAR _Result = AND( wOffice =TRUE(), wEMS = FALSE() )
RETURN
   _Result

That basic pattern can also be applied to a measure
For example:

Office wo EMS =
VAR String = SUBSTITUTE( SELECTEDVALUE( 'Sample'[Test Column] ), ", ", "|")
VAR wOffice = PATHCONTAINS( String, "office")
VAR wEMS = PATHCONTAINS( String, "EMS" )
VAR _Result = AND( wOffice =TRUE(), wEMS = FALSE() )
RETURN
   _Result

You could combine these 2 tests in a single column by adding a OR statement, of course.

I hope this is helpful.

4 Likes
3

Thanks,
The calculated column fits my needs perfectly. It also forces me to brush up on “pathcontains”.Which seems to be a very usefull measure that I am not overly familiar with.

4

Actually… i have a question as i am now playing with the data. I didn’t understand why you would create the first variable “substitute”. I tried removing it and simply using the column as the string. I come up with a different number?

5

Hi @chad.sharpe,

Path functions rely on the delimiter being a: “|”
Your string contains a “,” this explains both the need for SUBSTITUTE as well as the difference in result.

I hope this is helpful.

1 Like
6

Perfect, i assumed as much but i didn’t see that syntext when I was creating the calculated column.