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

     

     

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi lastnn30 

    you may also try like:

    Gender_full = IF([Gender]="M", "Male", "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]
    )