A short explanation of how CRM Analytics selects child rows when augmenting or joining a master table, and practical guidance to control which child row is used.
Background
When you augment or join a master table with a child table in Salesforce CRM Analytics (Tableau CRM), the system must decide which child row(s) to associate with each master row. The result depends on whether you use a “Look Up Multiple Values” (multi-value) option or a single-value lookup in dataflows and recipes. Understanding this behaviour helps you avoid unexpected aggregates or incorrect dimensions in dashboards and lenses.
How CRM Analytics behaves
Look Up Multiple Values
When you enable “Look Up Multiple Values” (multi-value):
- Numeric (measure) fields from the child table are summed across all matching child rows.
- Dimension (text) fields will retrieve all matching values. In the recipe you will see multiple values, but in a Lens or dashboard only one value is often shown (the UI displays a single representative value).
Look Up Single Values (default)
When you do not use multi-value lookups (the default single-value lookup):
- The lookup returns a single child row per master key.
- In many cases the platform picks the first matching record based on its internal ordering. In practical tests this equates to:
- If a numeric field is present, the system will pick the row with the lowest numeric value in sort order.
- If no numeric sort field exists, it will effectively pick the alphabetically first text value (ascending).
- This means you can get unpredictable results unless you explicitly control ordering or pre-aggregate the child table.
Recipe behaviour
Recipes mirror the dataflow behavior with similar options:
- With “Look Up Multiple Values” enabled in a recipe, numeric fields aggregate (sum) and dimensions can show multiple values in the recipe interface (but a Lens may display only one).
- Without multi-value, the lookup returns a single child row. Tests show the platform may choose the child record with the lowest numeric value (when numeric fields exist) or the alphabetically first text value.
Best practices to control which child row is selected
- Pre-aggregate the child dataset: use group & aggregate (sum/max/min) to produce one row per master key before augment/join.
- Add an explicit sort key and use a deterministic method (e.g., computeRanking) to flag the preferred child row, then filter to that row before joining.
- Use Look Up Multiple Values when you want to preserve all child rows for numeric aggregation (e.g., total sales) and be aware that dimension behaviour in lenses may still show a single value.
- When you need a single representative child row (e.g., latest or highest value), compute that in a dataflow/recipe step first — don’t rely on implicit ordering.
- Test the join/augment in both the recipe and in a lens to confirm the displayed result matches expectations (UI display may differ from underlying dataset).
Examples & References
Helpful resources:
Key takeaways
- Look Up Multiple Values = sums numeric fields and returns multiple dimension values (may display one in UI).
- Look Up Single Values = returns one matching child row; platform tends to pick the lowest numeric or alphabetically first text unless you pre-aggregate or control ordering.
- Always pre-aggregate or flag the desired child row when you need deterministic results.
Why this matters: Choosing the correct lookup strategy prevents incorrect aggregations or misleading dimension values in lenses and dashboards. For Salesforce admins and developers building CRM Analytics dataflows and recipes, explicitly controlling aggregation and ordering is the most reliable way to ensure accurate analytics for business users.