Forum Discussion

ianmonat's avatar
ianmonat
Helper I
9 years ago
Solved

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 table

Need Help
  • ianmonat's avatar
    ianmonat
    9 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

  • ianmonat's avatar
    ianmonat
    Helper I
    9 years ago

    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.