Forum Discussion
Grouping based on substring
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 - 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
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