cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bnb99 Frequent Visitor
Frequent Visitor

Grouping data with specific text in a column

Currently, I have a chart that looks like this (1st image). The data behind the chart is grouping of injuries illnesses. This is meant to to be an autopopulated report so when I click refresh, new data does not automatically group in to the appropriate groups hence why there is a slice for Sprains, and right next to it Sprains (Forearm). I am trying to find a way to have power BI group the the values without using grouping, and with DAX or a new column. Additionally, the rows are seperated out if one incident had multiple injuries. (2nd image). As you can see, some records are duplicated because two injuries Sprains (leg) and sprians (Arm) are considered two injuries per one event.

 

I have used  (DISTINCTCOUNT('Data'[Record No])) to gourp the number of records together, but the second step is to give a high level overview of the type of injuries that are occuring like 15 sprains in a month rather than, 1 sprain to the finger (Sprains(Finger)), 2 sprains to the knee (Sprains(Knee).

 

Chart1.PNGChart using "grouping"Chart2.PNGData showing duplicate record numbers(column1) with diffrent injuries (column2)

1 ACCEPTED SOLUTION

Accepted Solutions
mnayar Established Member
Established Member

Re: 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.

7 REPLIES 7
Super User
Super User

Re: Grouping data with specific text in a column

Not sure what the question is here. If you want to group items, you can create a new group based upon your column and group all things like "Sprains" together. Alternatively, you could create a custom column that does this based upon some formula where you use SEARCH or FIND perhaps. Again, not sure what you are going for here exactly.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Bnb99 Frequent Visitor
Frequent Visitor

Re: Grouping data with specific text in a column

Hi Greg,

 

So the first chart uses the grouping, but when the data auto populates, it seperates out the new data and I have go back in and manually group them. I am trying to avoid this as the data is entered daily.

 

In an attempt to clarify, I am trying to duplicate the 1st picture but with the apprpriate DAX formula. Moreover, I am trying to ensure the numbers are accurate. The record number duplicates when multiple injuries are entered in to the database. Although there was a sprain to multiple parts of the body, I still want to count it as one event.

 

Record NoDateInjury
2445/23/2018Rash
3126/1/2018Strain

 

I hope this clarifies a bit.

Bnb99 Frequent Visitor
Frequent Visitor

Re: Grouping data with specific text in a column

I hope I havent stumped everyone!

 

Is there a DAX formula that would address my concern?

mnayar Established Member
Established Member

Re: 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.

Super User
Super User

Re: Grouping data with specific text in a column

I'm still not clear on what is desired or what the rules are.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Bnb99 Frequent Visitor
Frequent Visitor

Re: Grouping data with specific text in a column

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....Pie delimeter.PNGDelimeter.PNG

mnayar Established Member
Established Member

Re: Grouping data with specific text in a column

@Bnb99 If my solution was the required one, plz mark it as the accepted solution.