Problem with DAX IFERROR formula

[juhoneyighot]

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.

[v-shex-msft · Accepted solution]

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

[tamerj1]

@juhoneyighot 

Maybe try IF ( ISERROR (.....

[juhoneyighot]

@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]

@juhoneyighot 

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

[juhoneyighot]

@tamerj1 

I have this "JobType column" in Column F see screesnhot below

Lets say it's NC 110_Estimates__70RF Final Billing. I have to get how many characters from that text until Phase_. Estimates which is the phase then _. So the Phase _ for here is 17 which is the total character of this (NC 110_Estimates_)

I hope I don't give you confusion on this.

[tamerj1]

@juhoneyighot 
Maybe somthing like this?

# of _ = 
VAR Length = 
    LEN ( 
        PATHITEM ( 
            SUBSTITUTE ( stx__legacy_fsjob[_JobType_txt], "__", "|" ),
            1
        )
    ) + 1
RETURN
    IF ( Length > 1, Length )

But why ddo you need this count for in the first plave? What is your ultimate goal?

[tamerj1]

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

[juhoneyighot]

@tamerj1 

This error occurs when I tried the formula

[tamerj1]

@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]

@tamerj1 

Hello! this error occurs.