using substitute () to change 2 values.

[lastnn30]

Hi

I have a column called Gender which has values like M, and F. I want to use Subsitute() to replace M to Male and F to Female. So what I created a New Column and I wrote this dax

Gender_full = subsitute(Table1(Gender), "M", "Male"")

But that only change M to Male, how can I write one Dax function to subsitute both M and F at same time.

Thank you very much.

[Greg_Deckler · Accepted solution]

@lastnn30 You might kick yourself:

Gender Full Column = 
    SUBSTITUTE(
        SUBSTITUTE([Gender],"M","Male"),
        "F", "Female"
    )

[smpa01 · Accepted solution]

@lastnn30  you can create a mapping table and utilize moving forward

Column =
VAR mapping =
    DATATABLE (
        "lookup", STRING,
        "val", STRING,
        {
            { "Male", "M" },
            { "M", "M" },
            { "F", "F" },
            { "Female", "F" }
        }
    )
RETURN
    MAXX (
        FILTER ( mapping, [lookup] = CALCULATE ( MAX ( 'Table'[Column1] ) ) ),
        [val]
    )

[FreemanZ · Accepted solution]

hi @lastnn30 

you may also try like:

Gender_full = IF([Gender]="M", "Male", "Female")

[smpa01 · Accepted solution]

@lastnn30  you can create a mapping table and utilize moving forward

Column =
VAR mapping =
    DATATABLE (
        "lookup", STRING,
        "val", STRING,
        {
            { "Male", "M" },
            { "M", "M" },
            { "F", "F" },
            { "Female", "F" }
        }
    )
RETURN
    MAXX (
        FILTER ( mapping, [lookup] = CALCULATE ( MAX ( 'Table'[Column1] ) ) ),
        [val]
    )

[Greg_Deckler · Accepted solution]

@lastnn30 You might kick yourself:

Gender Full Column = 
    SUBSTITUTE(
        SUBSTITUTE([Gender],"M","Male"),
        "F", "Female"
    )