cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianmonat
Helper I
Helper I

Replace single text value with multiple text values

Hello, I track my organization's sales activity and I've got an issue where we had some personnel changes to the sales team. 

 

Referencing the screenshot below, if you see the 3rd column 'DVP', there is a value called 'East DVP'.

 

That position was recently split into two positions, so now there is a NorthEast DVP (New Value 1), and a SouthEast DVP (New Value 2).

 

When I use Power BI's Replace Values feature, I can only replace a single value with another single value, but I need two new values. I don't want to create another column or table if I don't have to. If there is a DAX-based solution, the condition can be based on the values in the 2nd column, 'Regional Mgr', which are concealed for privacy.

 

Thanks in advance for your help!

 

 screenshot of salesperson lookup tablescreenshot of salesperson lookup table

1 ACCEPTED SOLUTION
ianmonat
Helper I
Helper I

Thank you for your suggestions, I found a much easier solution that required no DAX, I inserted a Conditional Column and based the condition from the Regional Mgr column. 

View solution in original post

3 REPLIES 3
ianmonat
Helper I
Helper I

Thank you for your suggestions, I found a much easier solution that required no DAX, I inserted a Conditional Column and based the condition from the Regional Mgr column. 

Anonymous
Not applicable

Hi 

 

 First, use the replace transform and replace your value as like below.

 

  East DVP -> NorthEast DVP,  SouthEast DVP

 

 Second, use the below script and you need to modify the table and column names.

 

NewTable = SUMMARIZE(
        ADDCOLUMNS(
            FILTER(
                CROSSJOIN(
                     SUMMARIZE( 
                            Sub,
                            Sub[Name],
                            Sub[Subject],
                            "SubCount",1+LEN(Sub[Subject])-LEN(SUBSTITUTE(Sub[Subject],",","")))
                ,dummy)
            ,dummy[dummy]<=[SubCount])
        ,"Subject Name",PATHITEM(SUBSTITUTE(Sub[Subject],",","|"),dummy[dummy]))
, Sub[Name]
,[Subject Name])

in the above script, "Sub" and "dummy" are tables. Let me know if you need any other help.

 

Thanks

Hari

Anonymous
Not applicable

Hi

 

  Check the below link, it will help you for your requirement.

 

http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

 

Thanks

Hari

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.