Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Grouping based on substring

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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