topic Calculate function for contains and does not contain at the same time. in DAX Commands and Tips

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

sharpedogs — Thu, 30 Jul 2020 03:06:28 GMT

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

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

amitchandak — Thu, 30 Jul 2020 04:17:20 GMT

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)

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

Anonymous — Thu, 30 Jul 2020 06:24:05 GMT

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)

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

sharpedogs — Mon, 03 Aug 2020 01:49:05 GMT

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