topic Re: CONTAINS function - Boolean error in DAX Commands and Tips

CONTAINS function - Boolean error

bmk — Mon, 29 Aug 2022 16:11:29 GMT

The following formula works, but I'm trying to better the approach a little since strings around it can change dynamically at the source, but the integer code pertaining to a category (233333, 455555 in the below example) will not.

Current formula :

Time Received =

IFERROR(

CALCULATE(

[Total Time],

Fact Table[Category] = "CATEGORY1-233333" ||

Fact Table[Category] = "CATEGORY 2 - 455555" )

,0)

New formula :

Time Received =

IFERROR(
CALCULATE(

[Total Time],

CONTAINS(Fact Table,Fact Table[Category],"310251",Fact Table[Category],"320251")

)
,0)

Not quite sure if the syntax is correct since the first formula is an OR condition.

Currently getting the error : "A function 'CONTAINS' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Re: CONTAINS function - Boolean error

daXtreme — Mon, 29 Aug 2022 19:08:59 GMT

You cannot put a true/false statements under CALCULATE. There are very specific conditions that can be written like this but they really are not true/false statements since the engine expands them automatically into tables. Because - let me reiterate it - only tables can be filters under CALCULATE. So, instead of CONTAINS in there you should turn it into a filter on a table and put the table instead. But I think you should use a different function here, CONTAINSSTRING, like so:

CALCULATE( [Total Time], FILTER( VALUES( 'Fact Table'[Category] ), OR( CONTAINSSTRING( 'Fact Table'[Category], "310251" ), CONTAINSSTRING( 'Fact Table'[Category], "320251" ) ) ) )

Re: CONTAINS function - Boolean error

bmk — Mon, 29 Aug 2022 19:16:52 GMT

Thank you. Can you explain your logic behind using VALUES function here?

Since I was thinking along the lines of :

CALCULATE(
[Total Time],
CONTAINSSTRING(Fact Table[Category],"233333") || CONTAINSSTRING(Fact Table[Category],"455555")
)