Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Regexp_replace function in DAX

Hi, I have column where data is as shown below, for this column "REGEXP_REPLACE(Promotion,'LA EM LAN | Nov 19','')" function is used in google visual stuido to achive a new column with data like "Es...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    This is because your column mg2[promotion] has rows where LAN or Nov are not present.

     

    Try this Calculated Column

     

     

    Column = 
VAR FirstLAN =
    Find (
        "LAN",
        'Table'[Promotion],
        1,
        LEN('Table'[Promotion]))
    
VAR FirstNov =
    FIND (
        "Nov",
        'Table'[Promotion],
        1,
        LEN('Table'[Promotion]))
    
RETURN
//FirstLAN & " " & FirstNov
SWITCH(
    TRUE(),
    FirstLAN = FirstNov || FirstLAN > FirstNov, " ",
    FirstNov > FirstLAN , 
    MID (
        'Table'[Promotion],
        FirstLAN + 3 , -- to adjust LAN (3)
        FirstNov - FirstLAN - 3 -- to adjust LAN (3)
    )
)

     

     

    Regards,

    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Harsh,

 

Thank you for the reply, I tried using your DAX but it is throwing me the below error.

I googled about this error and found that adding Iferror to find,mid will work, I tried that but it is throwing me another error 

"Expressions that yield variant data-type cannot be used to define calculated columns"

and this is because i am giving string "LAN"  and interger 1 in the same calculated column.

Please suggestme some other alternative

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

This is because your column mg2[promotion] has rows where LAN or Nov are not present.

 

Try this Calculated Column

 

 

Column = 
VAR FirstLAN =
    Find (
        "LAN",
        'Table'[Promotion],
        1,
        LEN('Table'[Promotion]))
    
VAR FirstNov =
    FIND (
        "Nov",
        'Table'[Promotion],
        1,
        LEN('Table'[Promotion]))
    
RETURN
//FirstLAN & " " & FirstNov
SWITCH(
    TRUE(),
    FirstLAN = FirstNov || FirstLAN > FirstNov, " ",
    FirstNov > FirstLAN , 
    MID (
        'Table'[Promotion],
        FirstLAN + 3 , -- to adjust LAN (3)
        FirstNov - FirstLAN - 3 -- to adjust LAN (3)
    )
)

 

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This worked, thank you so much