Forum Discussion

johnwedeles's avatar
johnwedeles
Icon for Helper III rankHelper III
6 years ago
Solved

Replacing text in column with new text based on condition

Hello:   I am trying to replace a hyphen ("-") with an en dash in an existing column. I know I'd need to use UNICHAR for this. So, I imagine the formula would be something like this:   IF Table N...
Need Help
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    johnwedeles 

    if it is a value in a cell, use substitute

    Column = SUBSTITUTE('Table'[Column1],"-",UNICHAR(8211))

    if it is the name of a measure, you replace the whole measure name

     

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
6 years ago

johnwedeles 

you can use DAX to create a column

Column = SUBSTITUTE("-",'Table'[Column1],UNICHAR(8211))

 

  • johnwedeles's avatar
    johnwedeles
    Icon for Helper III rankHelper III
    6 years ago

    Thank you, Greg_Deckler  and ryan_mayu . I tried both the REPLACE and SUBSTITUTE functions, and unfortunately when I try to select the appropriate table and column, the specific column does not show up as an option. Note, it is a string variable. I get an error message saying that "a single value for this column cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      johnwedeles - If you are creating a measure, then you will need to wrap the column reference in an aggregator like MAX, MIN, etc.

      • johnwedeles's avatar
        johnwedeles
        Icon for Helper III rankHelper III
        6 years ago

        Thank you, Greg_Deckler . I am entering this code but it is not working - is the right placement for the MAX function?

         

        Measure = SUBSTITUTE(MAX('Performance rates'[New Rate Definition])"-",UNICHAR(8211)))