cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## CONTAINS function - Boolean error

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 :

IFERROR(
CALCULATE(
[Total Time],
Fact Table[Category] = "CATEGORY1-233333" ||
Fact Table[Category] = "CATEGORY 2 - 455555" )
,0)

New formula :

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."
1 ACCEPTED SOLUTION
Solution Sage

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"
)
)
)
)``````
2 REPLIES 2
Helper II

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")
)
Solution Sage

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"
)
)
)
)``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors