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. Jobtype NC.IN Phase Status NC 120_Rough Builder Walk__10Not Started ...
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...
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: