Reference columns vs lookup columns — when to use which?

Started by Omar Hassan · 1 year ago · 3 replies · 2 views

Omar Hassan Participant

1 year ago

I see options for 'Reference Column' and what seems like a lookup pattern in formula columns. When should I use each? Are they functionally different?

3 Replies

Sort

Omar Hassan

Data Engineering Lead · InfraCore MENA · 1 year, 5 months ago

One practical difference: Reference columns support 'Match Multiple' — returning multiple values from all matching records in the source table. This is useful for one-to-many relationships where LOOKUPVALUE() would only return the first match.

Up

0

Anika Bose

Analytics Practice Lead · Vantage Analytics India · 1 year, 5 months ago

Use Reference columns for stable relationships (e.g., product name from product ID). Use formula-based lookup for derived values (e.g., price × quantity from the rate table) where you also want to apply a transformation.

Up

0

Raj Subramaniam

Microsoft Fabric Consultant · CloudBridge Solutions · 1 year, 5 months ago

Reference column: a visual-only column that displays a value from a related table based on a matching column. Non-editable, auto-updates when the source table changes. Use for displaying related data without duplicating it. Lookup in formula: a calculated column using LOOKUPVALUE() or similar. More flexible (can apply transformations) but is computed at display time rather than stored.

Up

0