Intermittent error: DAX expression with SSAS on prem server

Question

I am currently working at updating Tabular model hosted on-prem on a SASS server version 13.0. The tabular model is version 1200. We have complex logic created in our measures that allow us to define counting rules or to filter depending on parameters selected in disconnected tables. The model was created using snowflake ‘like’ schema: It can be simplified as below

Link to image: https://ibb.co/WfkmNPV

The error returned by DAX Studio or PowerBI (on an intermittent basis) is the following:

MdxScript(Model) (5174, 18) Calculation error in measure 'Admission'[Number of Clients]: Function 'CONTAINS' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

Sometimes error is resolving by itself without any doing from our end or (sometimes) by reprocessing the model. In some instances, all our measures are returning this error. In other instances, it’s only a few of them.

The DAX expression for 'Admission'[Number of Clients] is as below:

Admission[Number of Clients] := 
VAR MinDate = MIN ( 'Date'[Full Date] )
VAR MaxDate = MAX ( 'Date'[Full Date] )
VAR AdmissionDate =
    FILTER (
        Admission,
        SWITCH (
            TRUE (),
            VALUES ( 'Counting Rules'[Counting Rule] ) = "Starts",
                Admission[Start Date] >= MinDate && Admission[Start Date] <= MaxDate,
            VALUES ( 'Counting Rules'[Counting Rule] ) = "Ends",
                Admission[End Date] >= MinDate && Admission[End Date] <= MaxDate,
            VALUES ( 'Counting Rules'[Counting Rule] ) = "Active",
                Admission[Start Date] <= MaxDate && Admission[End Date] >= MinDate,
            0
        )
    )
RETURN
IF (
    COUNTROWS ( VALUES ( 'Counting Rules'[Counting Rule] ) ) = 1,
    CALCULATE (
        DISTINCTCOUNT ( 'Admission'[ClientKey] ),
        FILTER (
            AdmissionDate,
            IF ( RELATED ( 'Clients'[Date Of Birth] ) <= MAX ( 'Date'[Full Date] )
                    && ( ISFILTERED ( 'Client Age'[Age Band] ) || ISFILTERED ( 'Client Age'[Age] ) ),
                IF ( COUNTROWS ( VALUES ( 'Age Counting Rule'[Age Counting Rule] ) ) = 1,
                    INTERSECT (
                        VALUES ( 'Clients Age'[Age] ),
                        SELECTCOLUMNS (
                            ADDCOLUMNS (
                                VALUES ( Admission[ClientKey] ),
                                "Age",
                                    ROUNDDOWN (
                                        DATEDIFF (
                                            CALCULATE ( MAX ( 'Clients'[Date Of Birth] ) ),
                                            VAR AdmissionDateFiltered =
                                                IF (
                                                    VALUES ( 'Age Counting Rule'[Age Counting Rule] ) = "Age Min",
                                                    CALCULATE ( MIN ( Admission[Start Date] ), AdmissionDate, EARLIER ( Admission[ClientKey] ) = Admission[ClientKey] ),
                                                    CALCULATE ( MAX ( Admission[End Date] ), AdmissionDate, EARLIER ( Admission[ClientKey] ) = Admission[ClientKey] )
                                                )
                                            RETURN
                                                IF ( VALUES ( 'Age Counting Rule'[Age Counting Rule] ) = "Age Min",
                                                    IF ( AdmissionDateFiltered < MIN ( 'Date'[Full Date] ) && NOT ISBLANK ( AdmissionDateFiltered ),
                                                        MIN ( 'Date'[Full Date] ),
                                                        AdmissionDateFiltered
                                                    ),
                                                    IF ( AdmissionDateFiltered > MAX ( 'Date'[Full Date] ) && NOT ISBLANK ( AdmissionDateFiltered ),
                                                        MAX ( 'Date'[Full Date] ),
                                                        AdmissionDateFiltered
                                                    )
                                                ),
                                            DAY
                                        ) / 365.25,
                                        0
                                    )
                            ),
                            "Age", [Age]
                        )
                    ),
                    BLANK ()
                ),
                TRUE ()
            ) //GOM Status
            && IF (
                ISFILTERED ( 'Guardianship Status'[Guardianship Type] ) || ISFILTERED ( 'Guardianship Status'[Guardianship Type Description] ) || ISFILTERED ( 'Guardianship Status'[Under Guardianship] ),
                CONTAINS (
                    VALUES ( 'Guardianship Status'[Guardianship Type] ),
                    'Guardianship Status'[Guardianship Type],
                    [Child Protection Order Active]
                ),
                TRUE ()
            )
        )
    ),
    BLANK ()
)

The measure [Child Protection Order Active] in the GOM part of the expression is returning a string value. The modification I made to the logic is how the Age is calculated.

Have any of you encountered this error? How would you go by debugging something like that?

Thank you for you time in helping me.

Are you sure [Child Protection Order Active] measure always returns a string? How about forcing it with CONVERT([Child Protection Order Active], STRING) ? — Gigga
Yes, It's a IF statement which always return a string. That was my first look. To add a little bit a frustration, when using DAX studio to study the behaviour further: Defining the measure and then calling it to return result in a a table bring the result as expected. however not defining the measure and calling it directly from the model raise the error. — Remi

Remi Remi · Accepted Answer · 2021-06-23T02:42:37

I have resolved my issue by adding a condition around [Child Protection Order Active] to check that it doesn't return blanks. Measure is always returning a string (at least I assume considering the code) but for some odd reason, the engine think it may return blank (another assumption). Blank value is considered as integer and therefore returns an error with the above initial DAX expression.

Intermittent error: DAX expression with SSAS on prem server

1 Answers