[&]

The [&] operator creates a temporary parent containing only the elements shared by both of the parents named on either side of it.

Where [+] combines two parents and keeps everything under either of them, [&] keeps only the overlap. Anything belonging to one parent but not the other is excluded.

Example Hierarchy

An Employee dimension with two hierarchies built over the same people - one grouping them by department, the other by location:

Sales Employees
    Ana Silva
    Ben Carter
    Chloe Devi

Auckland Employees
    Ben Carter
    Chloe Devi
    Dan Okafor

Using the hierarchies above, the following expression:

Sales Employees[&]Auckland Employees

creates a temporary parent containing:

• Ben Carter
• Chloe Devi

Ana Silva is excluded - in Sales, but not based in Auckland. Dan Okafor is excluded - based in Auckland, but not in Sales. What survives the intersection is the overlap the two groupings share: the Auckland-based sales team.

Intersecting across hierarchies

The operator is at its most useful across two different hierarchies of the same dimension. Because a MODLR dimension is a flat list of elements with hierarchies built over it, the same leaf element can appear in several hierarchies at once - see Elements.

That makes each hierarchy a different way of categorising the same elements, and [&] asks which elements fall into both categories at once.

Reducing rows in a Workview

This is the main practical use, and it's a performance technique as much as a reporting one.

Consider a Workview showing employees, with Department and Location as title selectables. The obvious approach is to put the whole Employee dimension on rows, expand it, and let zero suppression hide everyone who isn't in the selected department and location.

That works, but the Workview builds every employee row first and then discards most of them. On a dimension of any size, the cost of assembling and suppressing those rows is paid on every refresh.

The alternative is to give the Employee dimension two hierarchies - one grouping employees by department, one grouping them by location - and ask for the intersection instead:

Sales Employees[&]Auckland Employees

Only the employees in both groups are ever requested, so the rows that suppression would have removed are never built. The suppression effort disappears because there is nothing left to suppress.

Driving it from the selectables

Since the department and location are on selectables, the expression can be built from the current context rather than hard-coded, so the rows follow whatever the user picks:

ELEMENT("Department") & " Employees" & "[&]" & ELEMENT("Location") & " Employees"

With Department on Sales and Location on Auckland, this resolves to Sales Employees[&]Auckland Employees.

Two different uses of & in one expression

The bare & is the concatenation operator joining the parts of the formula together. The "[&]" in quotes is literal text - the intersection operator being assembled into the element expression. They are unrelated despite looking alike.

Naming the grouping parents

The Employees suffix in that example is doing real work. The parents exist only to group the Employee dimension, but a parent named simply Sales or Auckland would collide with the actual elements of the Department and Location dimensions.

Keep names unambiguous across the model wherever you reasonably can. Where the same name means different things in different dimensions or hierarchies:

• Formulas need prefixing to resolve. A reference has to be qualified as Dimension:Hierarchy»Element so MODLR knows which one is meant - see Direct References.
• Cell addresses become hard to read. A cell address lists one element per dimension in the cube's dimension order, so a reader who doesn't know that order can't tell which dimension an ambiguous name belongs to.

Neither is fatal, and parent elements carry a hierarchy prefix in cell addresses regardless. But both are avoidable friction, and a distinguishing suffix costs nothing at the point the hierarchy is built.

Common Use Cases

• Reducing the rows a Workview builds, instead of suppressing them afterwards
• Finding elements common to two roll-ups
• Cross-referencing two hierarchies over the same dimension
• Narrowing a report to the overlap of two groupings
• Ad hoc "both of these" analysis without building a permanent hierarchy node

Notes

• The temporary parent exists only within the current calculation or view, and adds nothing to the underlying hierarchy.
• If the two parents share no elements, the result is an empty temporary parent.
• Intersection is order-independent: Parent 1[&]Parent 2 and Parent 2[&]Parent 1 return the same elements.