Grouping by Column Contains?

[chq]

My data looks somethng like this...

Transaction #	Type Code	Type	(Desired Column)
12	1000	Long	Long Term
12	101	Theatre	Long Term
12	1900	Baseball	Long Term
99	104	Theatre	Long Term
99	1001	Long 1	Long Term
61	104	Theatre	Theatre

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

[amitchandak]

@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")

[amitchandak]

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"}

[chq]

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

[amitchandak · Accepted solution]

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

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

[chq]

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

[chq]

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