Cardinality and cross-filter guide

In DataPrism, every relationship needs two decisions: its cardinality and its cross-filter direction. The Cardinality & cross-filter guide is the in-product reference for both, with worked examples, uniqueness rules and a cheat sheet. Open it while you set up a relationship, or when a report's filters do not travel the way you expect.

Open the guide

1. Open the Add relationship or Edit relationship dialog. For the steps that lead there, see Relationships and the core table.
2. Click Cardinality & cross-filter guide in the dialog's header.

Point at the button to read its tooltip: "See one-to-one and many-to-one cardinality, Single/Both cross-filter, and uniqueness rules. From table is always the fact table."

The guide opens over the dialog and is a reference: reading it changes nothing on your relationship. Set the values themselves with Cardinality and Cross-filter in the dialog behind it.

Move around the guide

• On a wide screen, the list sits in a 220 px column on the left, under the label Guide. On a narrow screen it becomes a row of chips under the title.
• The list holds Cardinality, N:1 · Many to one, 1:1 · One to one, Cross-filter, Single, Both, and Compare at the end.
• Click an item to jump to its section. The list marks the section you are reading as you scroll.
• The header states the ground rules: "Relationships connect bi tables for joins and report filtering. The from table is always the fact table. Pick one to one or many to one, then set cross-filter to Single or Both."
• Each card carries its number and title, badges, a Use case line, a summary, short notes and an example panel.
• Click Close in the corner to leave the guide.

The two cardinality patterns

Cardinality says how rows on the two sides of a join match. The relationship dialog offers the same two shapes under Cardinality, labelled N:1 and 1:1.

• Many to one (N:1). Each row in the fact (from) table matches at most one row in the target. Its badges read DEFAULT FILTER · BOTH and FROM · FACT TABLE. Uniqueness: "The target join column must not contain duplicate values (or null duplicates treated as duplicates)." The use case is a fact table pointing at a dimension, such as many order rows each pointing to one customer. N:1 is the default for a new relationship.
• One to one (1:1). Each fact row matches exactly one target row, and the other way round. Its badges read DEFAULT FILTER · BOTH and BOTH SIDES · UNIQUE. Uniqueness: both join columns must be unique. The use case is an extended profile, where each fact row has exactly one related settings row.

Both patterns allow Single or Both cross-filter, and Both is the default. On a one-to-one relationship, the form keeps it on Both; see Relationships and the core table.

The two cross-filter patterns

Cross-filter says which way a report filter travels along the relationship.

• Single. Carries the badge ONE DIRECTION. The summary reads "Filters propagate in one direction only." It is available for N:1 and 1:1 relationships, and the relationship dialog describes it as "Report filters propagate from source to target only." Its use case is directed reports: "You only want filters from the driving table to slice the related table, not the reverse." On N:1, the one side (target) column must remain unique.
• Both. Carries the badge DEFAULT · N:1 / 1:1. "Filters flow both ways. Default for N:1 and 1:1." Its use case is interactive dashboards: "Slicers on either table should filter the other (e.g. pick a region and see products; pick a product and see regions)." With Both on N:1, the target (one) column must stay unique; on 1:1, both join columns must stay unique.

When the table on the other side of a relationship, not the fact table, has row-level security, Cross-filter is forced to Both. See Row-level security and View as.

Read the example panels

Each card pairs its rules with an example: two small tables side by side, with a rail between them that shows which way filters travel, carrying labels such as "Both filters" or "Single A → B".

• The legend under the tables names four row states: Selected (the row you picked), Filtered match (a row the filter keeps), Duplicate / invalid (a key that duplicates where the rule requires uniqueness) and Not in filter (a row the filter leaves out, shown dimmed). The state colours the row's left edge.
• Short bullets walk each example through. The N:1 card says "Many order rows share the same customer key (N:1)." and warns that "The customer key on the one side must stay unique - duplicates are invalid." The 1:1 card says "Each user maps to exactly one profile row." The Single example shows one-way flow: "Selecting a customer does not flow back to orders."
• The examples are fictional: orders and customers for the N:1 card, users and profiles for the 1:1 card, and the same order and customer rows again for Single and Both. They do not read your model.

The cheat sheet

The last item in the guide's list, Compare, holds one table: the cheat sheet Cardinality & cross-filter at a glance, under the label Cheat sheet.

The default and the uniqueness cells are highlighted, so the two things to check stand out.

Questions, answered

How do I open the cardinality and cross-filter guide?

Open the Add relationship or Edit relationship dialog and click Cardinality & cross-filter guide in its header. How to reach the dialog is on Relationships and the core table.

Does the guide change my relationship?

No. The guide is a reference dialog: reading it changes nothing on your relationship. The dialog behind it is where you set Cardinality, Cross-filter and Active, and those changes apply only when you save.

What is the difference between many to one and one to one?

Many to one (N:1): many fact rows match one related row, so many order rows can point to one customer; only the target (one) column must be unique. One to one (1:1): each fact row matches exactly one target row and the other way round; both join columns must be unique. N:1 is the default for a new relationship.

Which join columns must be unique?

For N:1, the target (one) column: "The target join column must not contain duplicate values (or null duplicates treated as duplicates)." For 1:1, both join columns must be unique. The same rules apply when Both cross-filter is on.

What is the difference between Single and Both cross-filtering?

Single sends filters one way only, from the source to the target, and suits directed reports. Both lets filters propagate in both directions and suits interactive dashboards; it is the default for N:1 and 1:1. The two cross-filter cards carry the full wording.

Which side of a relationship is the fact table?

The from side. The guide repeats the rule in its header: "The from table is always the fact table." The Many to one (N:1) card also carries the FROM · FACT TABLE badge, and the relationship dialog fixes the from side in its From table (fact) section.

What do the badges on the cards mean?

• DEFAULT FILTER · BOTH (on both cardinality cards): Both is the default cross-filter direction.
• FROM · FACT TABLE (on Many to one (N:1)): the from table is the fact table.
• BOTH SIDES · UNIQUE (on One to one (1:1)): both join columns must be unique.
• ONE DIRECTION (on Single): filters travel one way only.
• DEFAULT · N:1 / 1:1 (on Both): Both is the default for N:1 and 1:1.

What do the colours in the example panels mean?

They name the four row states in the legend: Selected (the row you picked), Filtered match (a row the filter keeps), Duplicate / invalid (a key that duplicates where the rule requires uniqueness) and Not in filter (a row the filter leaves out, shown dimmed). The state colours each row's left edge.

What does the cheat sheet show?

Compare at the end of the guide's list shows Cardinality & cross-filter at a glance: both patterns in one table with their cross-filter options (Single, Both), the default (Both) and the uniqueness rule (target column for N:1, both columns for 1:1).

Do the examples show my data?

No. The panels use small fictional tables: orders and customers for the N:1 card, users and profiles for the 1:1 card, and the same order and customer rows for Single and Both. They illustrate the rules; they do not read your model.