topic Re: DAX Measure: If contains text and does not contain in DAX Commands and Tips

DAX Measure: If contains text and does not contain

adam_mac — Mon, 19 Jul 2021 11:39:48 GMT

Hi, i am trying to write a dax measure that returns a 1 or 0 (true, false) when the Subject column contains a bunch of options (apple, microsoft, amazon etc.) and when the Category column does not contain Active. Im getting a bit out of my depth in terms of Dax skills however.

The OR section seems to be running fine, however when i try to include the AND not contains part, it isnt returning accurate results. Is there something im missing? Also is there a more efficient way of me writing this?

Missalocated Activities =
SUMX(
MergedActivities,
IF(
CONTAINSSTRING( MergedActivities[Subject], "Apple" ) ||
CONTAINSSTRING( MergedActivities[Subject], "Microsoft" ) ||
CONTAINSSTRING( MergedActivities[Subject], "Amazon" ) ||
CONTAINSSTRING( MergedActivities[Subject], "Google" ) ||
CONTAINSSTRING( MergedActivities[Subject], "Acer" ) ||
CONTAINSSTRING( MergedActivities[Subject], "HP" ) ||
CONTAINSSTRING( MergedActivities[Subject], "Lenovo" ) ||
CONTAINSSTRING( MergedActivities[Subject], "Samsung" ) &&
NOT(CONTAINSSTRING( MergedActivities[Category], "Active" )
),1,0))

Re: DAX Measure: If contains text and does not contain

ERD — Mon, 19 Jul 2021 11:50:31 GMT

Hi adam_mac ,

You need one more pair of brackets:

Missalocated Activities = SUMX( MergedActivities, IF( (CONTAINSSTRING( MergedActivities[Subject], "Apple" ) || CONTAINSSTRING( MergedActivities[Subject], "Microsoft" ) || CONTAINSSTRING( MergedActivities[Subject], "Amazon" ) || CONTAINSSTRING( MergedActivities[Subject], "Google" ) || CONTAINSSTRING( MergedActivities[Subject], "Acer" ) || CONTAINSSTRING( MergedActivities[Subject], "HP" ) || CONTAINSSTRING( MergedActivities[Subject], "Lenovo" ) || CONTAINSSTRING( MergedActivities[Subject], "Samsung" ) ) && NOT(CONTAINSSTRING( MergedActivities[Category], "Active" ) ),1,0))

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: DAX Measure: If contains text and does not contain

Samarth_18 — Mon, 19 Jul 2021 12:04:54 GMT

Hi adam_mac

You can try below code:-

Missalocated Activities = SUMX( MergedActivities, IF( AND( (CONTAINSSTRING( MergedActivities[Subject], "Apple" ) || CONTAINSSTRING( MergedActivities[Subject], "Microsoft" ) || CONTAINSSTRING( MergedActivities[Subject], "Amazon" ) || CONTAINSSTRING( MergedActivities[Subject], "Google" ) || CONTAINSSTRING( MergedActivities[Subject], "Acer" ) || CONTAINSSTRING( MergedActivities[Subject], "HP" ) || CONTAINSSTRING( MergedActivities[Subject], "Lenovo" ) || CONTAINSSTRING( MergedActivities[Subject], "Samsung" )), NOT(CONTAINSSTRING( MergedActivities[Category], "Active" ) ) ),1,0))

Thanks

Re: DAX Measure: If contains text and does not contain

Fowmy — Mon, 19 Jul 2021 12:11:03 GMT

adam_mac

You can add a bracket to separate two sets of conditions, ( Check brands ) && (Check Active ) or Use the following meaure

Measure = SUMX( MergedActivities, INT( MAXX({"Apple","Microsoft","Amazon","Google","Acer","HP","Lenovo","Samsung"}, SEARCH([Value], MergedActivities[Subject],,0)) > 0 && SEARCH("Active", MergedActivities[Subject],,0) = 0 ) )