Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@chq , Please find the link to pbix. Minor chnage in column formula
https://www.dropbox.com/s/ghbeh6z55erxdtd/GroupTeam.pbix?dl=0
@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")
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"}
@amitchandak I am not sure I understand can you give me an example formula with that logic also applied? Thanks
@chq , Please find the link to pbix. Minor chnage in column formula
https://www.dropbox.com/s/ghbeh6z55erxdtd/GroupTeam.pbix?dl=0
@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?
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |