cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neelofarshama
Post Partisan
Post Partisan

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 
pbi.PNG

1 ACCEPTED SOLUTION

Hi @neelofarshama ,

 

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)

View solution in original post

5 REPLIES 5
harshnathani
Super User
Super User

Hi @neelofarshama ,

 

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

 

1.jpg

 

Regards,
Harsh Nathani

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

 

 

Hi Harsh,

 

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

pbi_1.PNG

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

Hi @neelofarshama ,

 

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)

This worked, thank you so much

amitchandak
Super User
Super User

@neelofarshama , refer if this can help

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



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors