Replacing text in column with new text based on condition

[johnwedeles]

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 Name Column Name contains "-", replace with UNICHAR(8211)

I don't mind creating a new column or measure if that is necessary.

Thank you!

[ryan_mayu]

@johnwedeles 

you can use DAX to create a column

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

 

[johnwedeles]

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]

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

[johnwedeles]

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

[ryan_mayu]

@johnwedeles 

plesae share the sample data and expected output.

[johnwedeles]

@ryan_mayu the string I am working with appears as follows:

Ambulatory Care: Emergency Department (ED) Visits: Ages 0-19 Years

This is an example of several (approximately 40) measures that are in the table. What I would like to do is replace the hypen (Ages 0-19 Years) with an en dash, using UNICHAR.

[ryan_mayu]

@johnwedeles 

why this is a measure? not a value in a cell? How did you calculate this? you can just paste your sample data here or paste a screenshot

[johnwedeles]

Thank you, @ryan_mayu . Here are two screenshots.

[ryan_mayu · Accepted solution]

@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

[johnwedeles]

Thank you, @ryan_mayu !

[johnwedeles]

Thank you, @ryan_mayu . I don't have the ability to attach a file here. 

[Greg_Deckler]

To do this in DAX, just use a new calculated column with REPLACE

https://docs.microsoft.com/en-us/dax/replace-function-dax