Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to count multiple value in one cell and creating new grouping

Hi all,

I have looked at other forums and saw that they used count rows with search function to calculate. But is there a way to also group/rename the occurence. 

 

The data I can extract is like the sample below: (which is behaviour of drivers recorded as per event).

EventIDBehavior
EUGS15420Braking, Driver Unbelted [Yard]
EUGS21193Rough/Uneven Surface
EUGS28897Rough/Uneven Surface, Cell Handheld - Observed
EUGS40268Accelerating
EUGS47610Braking, Driver Unbelted [Yard]
EUGS69334Braking, Driver Unbelted [Yard]
EUGS71831Other, Possible Collision
EUGS72333Accelerating, Driver Unbelted [Yard]
EUGS69334Other, Driver Unbelted [Yard]
EUGS69687Rough/Uneven Surface, Cell Handheld - Observed, Braking, Driver Unbelted [Roadway]

 

I want to create a table visual in Power BI that could count each behaviour seperated by a comma, and also create a new group for them: eg, Driver Unbelted should count both [Yard] and [Roadway] 

 

Sample output:

Braking - 4

Driver Unbelted - 6

Cellphone Observed - 2

Rough Condition - 3

Possible Collision - 1

Other - 2

etc...

 

Would I need to make another table to categories each behavior first?

 

Thanks in advance.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I ended up doing this, it might not be the best way as it takes very long time to define all the grouping but it works:

     

    In power query> split column by delimitor > unpivot the new columns > delete the atrribute column and keep the value only>

    save and apply

     

    Create new calculated column and use the code below to create my grouping

    BehaviorFilter = SWITCH(
        TRUE(),
        SEARCH("Key Word1", 'Table'[Behavior], 1, 0) > 0, "Group Name1",
        SEARCH("Key Word2", 'Table'[Behavior], 1, 0) > 0, "Group Name1",
        SEARCH("Key Word3",'Table'[Behavior], 1, 0) > 0, "Group Name2",
    "Other"
    )
     

    Hope it helps someone else.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I ended up doing this, it might not be the best way as it takes very long time to define all the grouping but it works:

     

    In power query> split column by delimitor > unpivot the new columns > delete the atrribute column and keep the value only>

    save and apply

     

    Create new calculated column and use the code below to create my grouping

    BehaviorFilter = SWITCH(
        TRUE(),
        SEARCH("Key Word1", 'Table'[Behavior], 1, 0) > 0, "Group Name1",
        SEARCH("Key Word2", 'Table'[Behavior], 1, 0) > 0, "Group Name1",
        SEARCH("Key Word3",'Table'[Behavior], 1, 0) > 0, "Group Name2",
    "Other"
    )
     

    Hope it helps someone else.