Forum Discussion

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

Get the characters from a text using DAX

Hello!

I need help to get the characters from a certain text using DAX. I have this Column "JobType". See table below.

JobtypeNC.INPhaseStatus
NC 120_Rough Builder Walk__10Not StartedNCNC 120_Rough Builder Walk10Not Started
NC.IN 720_Dehu_Estimate__90CompleteNC.INNC.IN 720_Dehu_Estimate90Complete
IN 622_CO EQ_QC__90CompleteININ 622_CO EQ_QC90Complete
Install_Estimates   

 I have to use a DAX to get the characters for the Phase and Status Column from Jobtype column with this condition. If Column NC.IN is "" then ""

 

else 

 

Phase column is extracted before the __(2underscores)

Status column is extracted after the __(2underscores)

 

I am confused what correct DAX formula to use. 

 

Thank you.

DAX Commands and Tips
  • Subash_Govind's avatar
    Subash_Govind
    2 years ago

    Hi juhoneyighot ,

     

    Below are DAX codes for creating new column,

    Phase =
    VAR BEFORE_DELIMITER = LEFT('DAX Custom column'[Job Type],(SEARCH("__",'DAX Custom column'[Job Type]))-1)
    RETURN IF('DAX Custom column'[NC.IN] = BLANK()," ",BEFORE_DELIMITER)
    Status =
    VAR AFTER_DELIMITER = RIGHT('DAX Custom column'[Job Type],(LEN('DAX Custom column'[Job Type])-SEARCH("__",'DAX Custom column'[Job Type]))-1)
    RETURN IF('DAX Custom column'[NC.IN] = BLANK(),BLANK(),AFTER_DELIMITER)
     
    It worked as below...

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi juhoneyighot ,

     

    in cases like this and you can, it makes more sense to do it in Power Query. Try to add a custom column like:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMDQyiA/KL03PUHAqzcxJSS1SCE/MyY6PNzTwyy9RCC5JLCpJTVHSASoFEzjUA+VQNcTqgEzX8/RTMAdqcEnNKI13LS7JzE0sSY2PtzRwzs8tyEktSQWbCVQFozFVA2WQlIOMBaoyMzKKd/ZXcA2MD3RGMw5sFpoSUs2AothYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Jobtype = _t, NC.IN = _t, Phase = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(
        Source, 
        "Custom", 
        each if [NC.IN]=""
        then ""
        else
        Text.AfterDelimiter( 
            [Jobtype], 
            "_", 
            Text.Length([Jobtype]) - Text.Length( Text.Remove([Jobtype], "_" ))-1
        )
    ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each if [NC.IN]=""
        then ""
        else
        Text.BeforeDelimiter(
            [Jobtype],
            "_",
            Text.Length([Jobtype]) - Text.Length( Text.Remove([Jobtype], "_" ))-2
        )
    )
in
    #"Added Custom2"

     

    it worked like:

     

  • Subash_Govind's avatar
    Subash_Govind
    Frequent Visitor
    2 years ago

    Hi juhoneyighot ,

     

    Below are DAX codes for creating new column,

    Phase =
    VAR BEFORE_DELIMITER = LEFT('DAX Custom column'[Job Type],(SEARCH("__",'DAX Custom column'[Job Type]))-1)
    RETURN IF('DAX Custom column'[NC.IN] = BLANK()," ",BEFORE_DELIMITER)
    Status =
    VAR AFTER_DELIMITER = RIGHT('DAX Custom column'[Job Type],(LEN('DAX Custom column'[Job Type])-SEARCH("__",'DAX Custom column'[Job Type]))-1)
    RETURN IF('DAX Custom column'[NC.IN] = BLANK(),BLANK(),AFTER_DELIMITER)
     
    It worked as below...