"Textlist" is one of my favorite formulas when trying to reduce sparsity in calculations. However, Source[TEXTLIST: Mapping] becomes ineffective because there isn't the option to rollup unique values. For anyone who ran into the same challenge before, hope this post can be helpful.
Use rank function to stage the data set before using “Source[TEXTLIST: Mapping]”
Data Set: The list of support tickets by opportunities. Each support ticket was assigned to a person.
End Goal: Calculate the connected revenue for each employee who worked on the support ticket for the opportunities. Revenue amount is available at opportunity level.
Steps: To get to the end goal without bringing in extra dimensions, I used the textlist formula to show the people who worked on the opportunity. The issue with directly using the “Assigned to EEID” field is that there are duplicated values. I don’t want to credit the same person multiple times for the same opportunity, so I need to stage the data to show just the unique value.
Step 1) - turn the text field into a numeric field. Assigned to EEID_Number = Value(Assigned to EEID)
Step 2) - using rank formula Assigned to EEID_Rank Seq = TEXT(RANK(Assigned to EEID_Number, ASCENDING, SEQUENTIAL, Assigned To EEID <> BLANK, Oppty_List)). Text() formula is optional, just my personal preference.
Step 3) - using another rank formula Assigned to EEID_Rank Min =TEXT(RANK(Assigned to EEID_Number, ASCENDING, MINIMUM, Assigned To EEID <> BLANK, Oppty_List)). Again Text() formula is optional, just my personal preference.
Step 4) - Leave only unique values Assigned to EEID_Unique = IF Assigned to EEID_Rank Seq = Assigned to EEID_Rank Min THEN Assigned To EEID ELSE BLANK
After completing these 4 steps, I was able to use Assigned to EEID_Unique to show the unique employees who worked on the opportunity using Assigned To EEID_Unique[TEXTLIST: Oppty_List] and credit each of them once with the opportunity amount.
First of all congratulations on being Master Anaplanner of the Year:)Please do not use TEXTLIST at all. It is the worst performing function out there and it breaches Planual Rule 2.02-11. As my mentor @rob_marshall says it is brutal.
In your case I think you can use
Assigned To EEID_Unique[FIRSTNONBLANK: Oppty_List] or
Assigned To EEID_Unique[LASTNONBLANK: Oppty_List]
Let me know your thoughts on this
Edit: Also if you wanted to see only Unique Values ISFIRSTOCCURRENCE would have done the trick
To elaborate on the sparsity benefit I mentioned. Here is how I typically go about it.
In places where I see one to many relationships, like the one below where one opportunity can be associated with a handful of people. I use textlist to aggregate up the unique values ("Specialist Sales Assistance" column) and then parse out each person. In the example below, there will be 7 columns, each column has a unique ID. Then I use the parsed data for calculation instead of nesting the people dimension with thousands of records.
Re: Get Unique Values Using Source[TEXTLIST: Mapping]
@Misbah Thanks for taking the time to read through the post! I am aware that Textlist is not an ideal function to use, however, FIRSTNONBLANK AND LASTNONBLANK is not going to work for this problem. If for every opportunity there is only one person assigned, then there is no issue with your solution. However, it is guaranteed that an opportunity will be touched by more than one person. I need the list of all the individuals who worked on it and get credit for the effort. FIRSTNONBLANK AND LASTNONBLANK will only pick the first person or the last and ignore everyone else. Same goes for ISFIRSTOCCURRENCE. Hope this helps clarify.
Textlist is one of the oldest formulas out there and it is really unavoidable as @nathan_rudman mentioned in these types of scenarios, I do think it is a good comprise to make to avoid sparsity knowing it might slow down model performance a bit and also has the10,000 characters limit.
Serena - would IsFirstOccurrence(), in your instance, work if you concatenated the opportunity with the employee ID? Using TextList() is, as @Misbah stated, absolutely brutal on performance and memory consumption. And yes, textlist() is one of the oldest functions we have, but might in fact be the worst, from a performance standpoint.