Forum Discussion

bmk's avatar
bmk
Helper II
3 years ago
Solved

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."
Help
  • daXtreme's avatar
    daXtreme
    3 years ago

    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

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    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"
            )            
        )
    )
)
  • bmk's avatar
    bmk
    Helper II
    3 years ago

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