IFERROR in DQ

[smpa01]

I am querying a datamart table and getting the following error

 Function 'IFERROR' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Sample Query

DEFINE
    COLUMN t1[__sid] =
        VAR int =
            IFERROR (
                VAR int = CONVERT ( t1[prop], INTEGER ) RETURN int,
                0
            )
        RETURN
            int & "-" & t1[__bid]
    TABLE cust =
        SUMMARIZECOLUMNS ( t1[ID], t1[__sid] )

EVALUATE
cust

How can I bypass this; as `t1[prop]` has mixed data types and IFERROR or equivalent of IFERROR needs to take place.

@AlexisOlson @bcdobbs @CNENFRNL 

[AlexisOlson · Accepted solution]

This isn't perfect but might be good enough...

You can format the mixed value as text and check if the first and last characters are digits before attempting to convert it to an integer.

VAR _Str = FORMAT ( t1[prop], "0" )
VAR _Digits_ = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" }
VAR _Int =
    IF (
        LEFT ( _Str ) IN _Digits_ && RIGHT ( _Str ) IN _Digits_,
        CONVERT ( _Str, INTEGER ),
        0
    )
VAR _Result = "prefix" & _Int
RETURN
    _Result

[AlexisOlson]

This seems a bit odd. You're converting it to an integer and then into a string?

Can you give some representative examples of t1[prop] and desired t1[__sid]?

[smpa01]

 

@AlexisOlson

[AlexisOlson · Accepted solution]

This isn't perfect but might be good enough...

You can format the mixed value as text and check if the first and last characters are digits before attempting to convert it to an integer.

VAR _Str = FORMAT ( t1[prop], "0" )
VAR _Digits_ = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" }
VAR _Int =
    IF (
        LEFT ( _Str ) IN _Digits_ && RIGHT ( _Str ) IN _Digits_,
        CONVERT ( _Str, INTEGER ),
        0
    )
VAR _Result = "prefix" & _Int
RETURN
    _Result

[smpa01 · Accepted solution]

@AlexisOlson  many thanks for the response and can't thank you enough for promptly replying. Apologies for the delayed response on my end.

Your approach is awesome. However bypassing DQ limitation for this particular use case is not easy. With your approach, I get this prompt now

Function 'CONTAINSROW' is not allowed as part of calculated column DAX expressions on DirectQuery models.

To bypass, instaed of the column, I have utilized my original expression in a Measure and it went through; e.g.

DEFINE
    TABLE cust =
        SUMMARIZECOLUMNS (
            t1[ID],
            "__sid",
                VAR int =
                    IFERROR ( VAR int = CONVERT ( t1[prop], INTEGER ) RETURN int, 0 )
                RETURN
                    int & "-" & t1[__bid]
        )

EVALUATE
cust

[AlexisOlson]

Yeah, I've had issues with DirectQuery calculated columns multiple times, so I try to avoid them whenever I can.

Does it work for you if you expand the IN to use a bunch of || instead?

VAR _Str = FORMAT ( t1[prop], "0" )
VAR _L = LEFT ( _Str )
VAR _R = RIGHT ( _Str )
VAR _Int =
    IF (
        (  
               _L = "0"
            || _L = "1"
            || _L = "2"
            || _L = "3"
            || _L = "4"
            || _L = "5"
            || _L = "6"
            || _L = "7"
            || _L = "8"
            || _L = "9"
        ) &&
        (
               _R = "0"
            || _R = "1"
            || _R = "2"
            || _R = "3"
            || _R = "4"
            || _R = "5"
            || _R = "6"
            || _R = "7"
            || _R = "8"
            || _R = "9"
        ),
        CONVERT ( _Str, INTEGER ),
        0
    )
VAR _Result = "prefix" & _Int
RETURN
    _Result