Forum Discussion
Grouping data with specific text in a column
Bnb99 If I am understanding correctly you are trying to split the 2nd column based on the delimeter bracket. If that is correct you can do that in power query
by selecting the table and then clicking edit query and then clicking on add column adn then extract, text before delimeter and you use "(" as that delimeter and you can get the body part the same way. Once you have the 2 columns you can create 2 charts and then use the high level chart to drill into the other. for example first chart will have Rash, Strains etc and the second chart would have the body part.
if you have some additional logic which needs to go in that could be your standard dax formula with switch or if.
If this is not the requested output plz give example of what the final output from original data you want.
I think you are on the right track with this. I want to show you what I've done so far just in case there are other suggestions!
So creating the two columns with the "before delimeter" and "between delimeter" worked wonderfully! This is excatly what I was looking for!
Now to ensure I do not have duplicates, I used the DAX "Record No - Group = (DISTINCTCOUNT('Data'[Record No]))" to ensure that its not counting the duplicate record numbers, thus duplicating multiple injuries (i.e. rash twice when it is under the same record number and be counted as 1). The DAX is in the "values" for the pie chart and the Legend represents the "text before delimeter" to show high level injuries.
I'm going to check against my raw data to ensure that it is counting correctley and will get back to you. I am so excited!
I apologize for not being the clearest. This is all so new....
Bnb99 If my solution was the required one, plz mark it as the accepted solution.