Forum Discussion

sharpedogs's avatar
sharpedogs
Icon for Advocate II rankAdvocate II
6 years ago
Solved

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

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's avatar
    amitchandak
    6 years ago

    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)

  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      sharpedogs
      Icon for Advocate II rankAdvocate II
      6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)