A practical look at how CRM Analytics (Tableau CRM) selects rows when augmenting or joining a master table to a child table — and how Look Up Multiple vs Look Up Single behave.
Overview
When you join or augment a master table with a child table in CRM Analytics, the join behavior depends on whether you use Look Up Multiple Values (multi-value) or Look Up Single Values (single-value). Understanding which child row is chosen helps avoid surprises in lenses and dashboards.
Behavior summary
- Look Up Multiple Values: For numeric (measure) fields, the dataflow/recipe aggregates (sums) the values across all matching child rows. For dimension (text) fields, the recipe will retrieve all matching values but a lens will only display one of the text values at a time — giving the appearance of a single value even though the dataset contains multiple.
- Look Up Single Values: The join returns a single matching child record. In documented behavior, the lookup returns the first matching record, but in practical tests the system often selects the row based on internal sort order: when a numeric field exists it picks the row with the lowest numeric value; if no numeric measure is present it sorts text alphabetically (ascending) and returns the first row.
Why this happens
Dataflows and recipes use internal indexing and aggregation rules to resolve many-to-one relationships. Multi-value lookups are designed to preserve all child values and aggregate numeric measures; single-value lookups must collapse multiple matches into one row and therefore apply a deterministic selection strategy (often influenced by sort order or available numeric fields).
Best practices & tips
- Prefer Look Up Multiple Values when you need sums or all child values preserved (and use explicit aggregation later in the recipe or lens).
- If you must use Look Up Single Values, prepare the child data so the intended row is clearly first — e.g., add a sorting field such as a timestamp or rank and keep only the desired row (use a transform to filter or compute a rank).
- In recipes, perform grouping and aggregation on the child table before joining to the master so the join behavior is explicit and repeatable.
- When Lens output looks “wrong”, inspect the dataset in the recipe (where multi-values are visible) rather than looking only at the lens display.
- For deterministic single-row selection, add a helper numeric field (e.g., -1 * timestamp as a numeric rank) or use computeExpression and filter nodes in dataflows to pick the latest or highest-priority child row before the augment.
Use cases
- Summing order line amounts into an account-level master — use Look Up Multiple Values and then aggregate.
- Picking the most recent status record — pre-aggregate or filter child records to the latest before joining with Look Up Single Values.
Why this matters: predictable joins prevent incorrect metrics and confusing dashboard results. Admins and developers should decide whether aggregation or strict single-row behavior is required, and design the recipe or dataflow accordingly to ensure repeatable outputs for business users and reports.