cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Grouping by Column Contains?

My data looks somethng like this...

Transaction #Type CodeType(Desired Column)
121000LongLong Term
12101TheatreLong Term
121900BaseballLong Term
99104TheatreLong Term
991001Long 1Long Term
61104TheatreTheatre



Is there a way to Group where the transaction number for all rows of the column has a subcategory based on the Type to get the desired column shown above? Meaning If the Type contains Long then all Transaction #s that have a row with Long the new column categorizes that Transaction number for all rows as Long Term

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@chq , Please find the link to pbix. Minor chnage in column formula

https://www.dropbox.com/s/ghbeh6z55erxdtd/GroupTeam.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

@chq , create a new column like

New column =
var _1 = countX(filter(table,[Transaction #]=earlier([Transaction #]) && [Type]="Type"),[Transaction #])
return
if(isblank(_1) || _1 =0,[Type],"Long Term")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@amitchandak  Would this be able to apply to multiple types, for example if it doesn't contain Long or Baseball but does contain Theatre then it equals Theatre?

Highlighted

I have Taken count of long , you have one more countx and create logic

or use in

[Type]="Long"

[Type]="Basketball"

 

or

[Type] in{"Long","Basketball"}



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@amitchandak I am not sure I understand can you give me an example formula with that logic also applied? Thanks

Highlighted

@chq , Please find the link to pbix. Minor chnage in column formula

https://www.dropbox.com/s/ghbeh6z55erxdtd/GroupTeam.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted

@amitchandak great this looks like it works correctly. Where I am confused is how would I add additional levels to this though? 

I can't have the non-designated values reflect as "Type", they will need to follow a logic  where if the new value is not Long then it becomes a different variable


For Example:


Calc = var _1 = COUNTX(FILTER(Sheet1,[Transaction #] =EARLIER([Transaction #]) && CONTAINSSTRING([Type],"Baseball")),[Transaction #])
Return if(ISBLANK(_1),[Type],"Baseball")



Would I be able to have it designate those as Baseball and if the new Calc is not Baseball and  [Type] contains Long then Cacl = Long?




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors