DAX Measure: If contains text and does not contain

[adam_mac]

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))

[Fowmy · Accepted solution]

@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
    ) 
) 

[Samarth_18]

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

[ERD]

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.