can anyone help me work out why this returns "FALSE" for all rows? (CONTAINS function)

[BlandJ]

Hi all,

I am trying to identify all rows of data that contain certain values within a text string on 2 diferent fields:

Jan 2024 deal = AND ((CALCULATE(CONTAINS('ICISCreativeTrack Targeting2023','ICISCreativeTrack Targeting2023'[End_Date],"2023-12-31"))), (CALCULATE(CONTAINS('ICISCreativeTrack Targeting2023','ICISCreativeTrack Targeting2023'[Start_Date],"2022-"))))

have I used the wrong function here?

Is there a better way to find a text string to help me identify the right deals - both date fields are "text" format and I can't transform it.

many thanks Jo 

[tamerj1]

Hi @BlandJ 
Please try

Jan 2024 deal =
NOT ISEMPTY (
    FILTER (
        'ICISCreativeTrack Targeting2023',
        AND (
            CONTAINSSTRING ( 'ICISCreativeTrack Targeting2023'[End_Date], "2023-12-31" ),
            CONTAINSSTRING ( 'ICISCreativeTrack Targeting2023'[Start_Date], "2022-" )
        )
    )
)

[BlandJ]

That's works nicely - can I use this result in a visual? It seems ot only want to bring it through as a value not a row or column header.... do I need extra stuff in here to return a value of Y or N if it's true or false?   I'm slowly getting to grips with  DAX code but this one is eluding me!  Many thanks Jo 

[tamerj1 · Accepted solution]

@BlandJ 

Technically yes you can but better to convert to text data type like

Jan 2024 deal =
IF (
ISEMPTY (
FILTER (
'ICISCreativeTrack Targeting2023',
AND (
CONTAINSSTRING ( 'ICISCreativeTrack Targeting2023'[End_Date], "2023-12-31" ),
CONTAINSSTRING ( 'ICISCreativeTrack Targeting2023'[Start_Date], "2022-" )
)
)
),
"No",
"Yes"
)

you may also use it as a count or visual level filter Like in

Jan 2024 deal =
COUNTROWS (
FILTER (
'ICISCreativeTrack Targeting2023',
AND (
CONTAINSSTRING ( 'ICISCreativeTrack Targeting2023'[End_Date], "2023-12-31" ),
CONTAINSSTRING ( 'ICISCreativeTrack Targeting2023'[Start_Date], "2022-" )
)
)
)

[BlandJ]

thank you!

It's way better but I now get this error:  A single value for column 'End_Date' in table 'ICISCreativeTrack Targeting2023' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, coun

The data looks like this: 

[FreemanZ]

hi @BlandJ 

try like:

Jan 2024 deal = 

AND (

    CONTAINSSTRING(

      'ICISCreativeTrack Targeting2023'[End_Date],

      "2023-12-31"

    ), 

    CONTAINSSTRING(

       'ICISCreativeTrack Targeting2023'[Start_Date],

       "2022-"

   )

)

the solution would be easier if sample data could be provided.