Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bnb99
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).

 

Chart using "grouping"Chart using "grouping"Data showing duplicate record numbers(column1) with diffrent injuries (column2)Data showing duplicate record numbers(column1) with diffrent injuries (column2)

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

Anonymous
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

I hope I havent stumped everyone!

 

Is there a DAX formula that would address my concern?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.