Regexp_replace function in DAX

[Anonymous]

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 "Essesntial CA", "Today Headlines", "Opinion", "Tasting Notes"  so on please help me to create a DAX for this requirement in power BI 

[harshnathani]

Hi @Anonymous ,

You should do this in Power Query.

But if you want to create a Calculated Column if your Values are between LAN and Nov.

Column = 
VAR FirstLAN =
    FIND (
        "LAN",
        'Table'[Promotion],
        1
    )
VAR FirstNov =
    FIND (
        "Nov",
        'Table'[Promotion],
        1
    )
RETURN

    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]

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

[harshnathani · Accepted solution]

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]

This worked, thank you so much

[amitchandak]

@Anonymous , refer if this can help

https://radacad.com/power-bi-and-regular-expressions