Forum Discussion

juhoneyighot's avatar
juhoneyighot
Icon for Helper III rankHelper 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...
  • 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

juhoneyighot's avatar
juhoneyighot
Icon for Helper III rankHelper 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
    Icon for Community Champion rankCommunity 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?

    • juhoneyighot's avatar
      juhoneyighot
      Icon for Helper III rankHelper III
      2 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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?