Calculate function for contains and does not contain at the same time.

[sharpedogs]

I have a column with a variety of license that each person has been assigned, as per column below.

I need a few measures so that I can report against certian license groupings, for example

1) Count rows that CONTAIN "Office 365" - it should count 2

2) Count rows that ONLY HAS "Office 365" - it should count 0

3) Count rows that CONTAIN "Office 365" and "Visio" - it should count 2  

4) Count rows that CONTAIN "Office 365" Does NOT Contain "Word" - it should count 1

Licenses

Office 365 + Word + Visio

Visio + Project

Office 365 + Visio

Project + Visio

[amitchandak · Accepted solution]

@sharpedogs , 1 and 2 and are contradictory

You can use https://docs.microsoft.com/en-us/dax/containsstring-function-dax

with Switch true()

example  -- Correct logic as per need. Order is also important

Switch (
CONTAINSSTRING('Table'[NAME],"Office 365") && CONTAINSSTRING('Table'[NAME],"Visio"), 2,
not(CONTAINSSTRING('Table'[NAME],"Office 365")) ,0 ,
CONTAINSSTRING('Table'[NAME],"Office 365") && not(CONTAINSSTRING('Table'[NAME],"Word")) , 1,
0)

[harshnathani · Accepted solution]

HI @sharpedogs ,

You can try these measures

Only has Office365 =
VAR _texttobefound = "Office 365"
VAR _lengthoftext =
    LEN ( _texttobefound )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            CONTAINSSTRING (
                'Table'[Licenses],
                _texttobefound
            )
                && LEN ( 'Table'[Licenses] ) = _lengthoftext
        )
    )

Contains Office365 & Visio =
VAR _texttobefound1 = "Office 365"
VAR _texttobefound2 = "Visio"
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            CONTAINSSTRING (
                'Table'[Licenses],
                _texttobefound1
            )
                && CONTAINSSTRING (
                    'Table'[Licenses],
                    _texttobefound2
                )
        )
    )

Contains Office365 & not Word =
VAR _texttobefound1 = "Office 365"
VAR _textnottobefound2 = "Word"
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            CONTAINSSTRING (
                'Table'[Licenses],
                _texttobefound1
            )
                && NOT CONTAINSSTRING (
                    'Table'[Licenses],
                    _textnottobefound2
                )
        )
    )

Contains Office365 =
COUNTROWS (
    FILTER (
        'Table',
        CONTAINSSTRING (
            'Table'[Licenses],
            "Office 365"
        )
    )
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak · Accepted solution]

@sharpedogs , 1 and 2 and are contradictory

You can use https://docs.microsoft.com/en-us/dax/containsstring-function-dax

with Switch true()

example  -- Correct logic as per need. Order is also important

Switch (
CONTAINSSTRING('Table'[NAME],"Office 365") && CONTAINSSTRING('Table'[NAME],"Visio"), 2,
not(CONTAINSSTRING('Table'[NAME],"Office 365")) ,0 ,
CONTAINSSTRING('Table'[NAME],"Office 365") && not(CONTAINSSTRING('Table'[NAME],"Word")) , 1,
0)

[sharpedogs]

Thanks.... that's exactly the function I needed...