Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
bmk
Helper II
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 :

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

View solution in original post

2 REPLIES 2
bmk
Helper II
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")
)
daXtreme
Solution Sage
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"
            )            
        )
    )
)

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors