Forum Discussion

Chrisblanc's avatar
Chrisblanc
Regular Visitor
7 years ago
Solved

Categorizing text values

Hello everybody,    I am currently struggling with something that seems very simple;    I have within my table of data a column called "Title" in with each row contains one value among "Mr", "Ms"...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Chrisblanc ,

     

    I'd like to suggest you create a mapping table for these descriptions.

     

    For example:

     

    Name, Desc
Man,Mr
Man,Sir
Woman,Ms
Woman,Mrs

     

    Then you can use name field of above table as category to create a visual and write a measure to compare with table who stored different descriptions and get the count.

     

    Compare = 
VAR tagged =
    ADDCOLUMNS (
        'Sample',
        "Type", CALCULATE (
            FIRSTNONBLANK ( Mapping[Name], [Name] ),
            FILTER ( ALL ( Mapping ), SEARCH ( Mapping[Desc], 'Sample'[Text], 1, -1 ) > 0 )
        )
    )
RETURN
    COUNTROWS ( FILTER ( tagged, [Type] IN VALUES ( Mapping[Name] ) ) )

     

     

    Regards,

    Xiaoxin Sheng

HotChilli's avatar
HotChilli
Community Champion
7 years ago
You could add a new column in power query editor. M or F depending on Title values. It's basically going to be a big 'if' statement. Once you have that working, you write measures in DAX to count the m and f rows and work out percentages