Forum Discussion

lastnn30's avatar
lastnn30
Post Patron
3 years ago
Solved

using substitute () to change 2 values.

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    lastnn30 You might kick yourself:

    Gender Full Column = 
    SUBSTITUTE(
        SUBSTITUTE([Gender],"M","Male"),
        "F", "Female"
    )
  • smpa01's avatar
    smpa01
    3 years ago

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

     

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    lastnn30 You might kick yourself:

    Gender Full Column = 
    SUBSTITUTE(
        SUBSTITUTE([Gender],"M","Male"),
        "F", "Female"
    )
  • smpa01's avatar
    smpa01
    Community Champion
    3 years ago

    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's avatar
    FreemanZ
    Super User
    3 years ago

    hi lastnn30 

    you may also try like:

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