Forum Discussion

juhoneyighot's avatar
juhoneyighot
Helper III
2 years ago
Solved

Problem with DAX IFERROR formula

I'doing this IFERROR DAX formula:

Phase _ = IFERROR(IF('stx__legacy_fsjob'[# of _]="","", FIND("~", SUBSTITUTE('stx__legacy_fsjob'[_JobType_txt],"_","~",'stx__legacy_fsjob'[# of _]-1))),'stx__legacy_fsjob'[_JobType_txt])

However, this error occurs: Expressions that yield variant data-type cannot be used to define calculated columns.

Does PowerBI has IFerror formula? I only saw IF and IF.EAGER. 

Thank you for the help.

  • Anonymous's avatar
    Anonymous
    2 years ago

    HI juhoneyighot,

    I think this should be more related to your data source connection mode. AFAIK, direct query mode has limited the Dax functions usage.
    For this sceniaro, you can't create calculate columns in direct query with complex Dax functions. I'd like to suggest you use measure expression instead.

    Regards,

    Xiaoxin Sheng

10 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    Hi juhoneyighot 
    Please try

    Phase _ =
IFERROR (
    IF (
        'stx__legacy_fsjob'[# of _] = BLANK (),
        "",
        FIND (
            "~",
            SUBSTITUTE (
                'stx__legacy_fsjob'[_JobType_txt],
                "_",
                "~",
                'stx__legacy_fsjob'[# of _] - 1
            )
        )
    ),
    'stx__legacy_fsjob'[_JobType_txt]
)
      • tamerj1's avatar
        tamerj1
        Community Champion
        2 years ago

        juhoneyighot 
        Please try

        Phase _ =
IFERROR (
    IF (
        'stx__legacy_fsjob'[# of _] = BLANK (),
        "",
        FIND (
            "~",
            SUBSTITUTE (
                'stx__legacy_fsjob'[_JobType_txt],
                "_",
                "~",
                'stx__legacy_fsjob'[# of _] - 1
            )
        ) & ""
    ),
    'stx__legacy_fsjob'[_JobType_txt]
)
    • juhoneyighot's avatar
      juhoneyighot
      Helper III
      2 years ago

      tamerj1 

      I think we can use convert formula to convert those string blank value to text  just as the same in below dAX FORMULA. but my problem is I am new to powerbi dax formula writing so I had a hard time doin this.

      # of _ =
      IF('stx__legacy_fsjob'[NC.IN]="","",CONVERT((LEN('stx__legacy_fsjob'[_JobType_txt])-(LEN(SUBSTITUTE('stx__legacy_fsjob'[_JobType_txt],"_","")))),STRING))
      Note:The issue was your expression have true part as blank string value and false part returning length which is numeric data type so we need to have both values of same type.

      • tamerj1's avatar
        tamerj1
        Community Champion
        2 years ago

        juhoneyighot 

        Apologies as I should have asked earlier, but what exactly are you trying to accomplish? Can you present a sample dummy data?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI juhoneyighot,

    I think this should be more related to your data source connection mode. AFAIK, direct query mode has limited the Dax functions usage.
    For this sceniaro, you can't create calculate columns in direct query with complex Dax functions. I'd like to suggest you use measure expression instead.

    Regards,

    Xiaoxin Sheng