Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
juhoneyighot
Helper III
Helper III

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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
Super User
Super User

@juhoneyighot 

Maybe try IF ( ISERROR (.....

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

@juhoneyighot 

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

@tamerj1 

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

juhoneyighot_0-1699015349209.png

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.

@juhoneyighot 
Maybe somthing like this?

1.png

 

# 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
Super User
Super User

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 

juhoneyighot_0-1698930426633.png

This error occurs when I tried the formula

@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 

juhoneyighot_0-1698933758844.png

Hello! this error occurs.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.