Grouping based on substring

[Anonymous]

Good Morning,

I am having a bit of trouble figurint this one out. 

I have a table called Escalations which contains one row per esclation in our product. 

One of the fields in that table is called "Category".  The values in  always start with a predefined value - for example here are some possible values for that field:

Admin- Permissions

Admin- Anuthoring

Coaching - Upload 

Coaching - Conversion

Coaching - Transcript

I want to create a table which will look like this:

Category               Number of Escalations in that category

Coaching              COUNTof all rows in the Escalations table that start with Coaching 

Admin                   COUNTof all rows in the Escalations table that start with Admin

I played around with Summarize but can't quite get it to work

Any help will be greatly appreciated.

Susan

[Anonymous · Accepted solution]

@Anonymous  - One way to do this is:

1. Change the name of your column to Subcategory and add a new column called Category. The new column would contain everything prior to the dash and then remove the ending whitespace.

You can create and clean this new Category column  in Power Query with Extract and Trim:

Alternatively, with DAX you can create a Calculated Column:

Category 2 = 
var dash_position = FIND(
    "-", 
    YourTable[SubCategory],
    1,
    100
)
var before_dash = MID(
    YourTable[SubCategory],
    1,
    dash_position-1
)
return TRIM(before_dash)

2. Your measure can then be a very simple one - count the number of rows in the table (or in some other related table):

Count YourTable = 
COUNTROWS(YourTable)

3. Add the new Category column and Measure to a visual.

Hope this helps,

Nathan

[Anonymous]

Thank you very much. 

I don't know why I did not think of it before and was overcomplicating it like that.

What I ended up doign actually is based on your suggestionI created a table that had the values I needed ( Coaching, Admin etc since I know then before hand.

I added a visual for the number of total rows and the component field from the hard coded table.

With my solition I have to maintain the reference table vs your solution is better because it is dynamic so I will re-work mine to match yours.

Thanks again

Susan