Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin 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.
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.
Solved! Go to 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
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
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.
Apologies as I should have asked earlier, but what exactly are you trying to accomplish? Can you present a sample dummy data?
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.
@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?
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
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]
)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
15 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
22 | |
11 | |
10 | |
10 | |
8 |