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

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)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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'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)