Star schema and relationships in Power BI

A star schema sorts every table in a semantic model into one of two kinds: dimension tables that describe things and are used to filter and group, and fact tables that record events and are used to summarize. The decision rule for exam questions: relate each dimension to the fact table with a one-to-many relationship, filtering in a single direction from the dimension (one side) to the fact (many side), and change that only when the scenario forces it. Microsoft’s guidance puts it as dimension tables enabling filtering and grouping, and fact tables enabling summarization.

Classify every table first

Take a retailer’s data and sort the tables before drawing any line:

Table	Kind	Why
Sales (one row per order line: keys, quantity, amount)	Fact	Records events, holds numbers to add up
Product, Customer, Store	Dimension	Describe who and what, with a unique key and descriptive columns
Date	Dimension	The most consistent dimension in any star schema

A fact table holds dimension keys plus numeric measure columns. The keys decide its dimensions, and the key values decide its grain, meaning what one row represents. A sales-target table keyed on date and product, with dates set to the first of each month, has a month-by-product grain. Microsoft states that a fact table should always load at a consistent grain. Mixing order-line rows and monthly summary rows in one table is the usual grain error.

There is no property in Power BI that marks a table as a fact or a dimension. The relationships decide it: the “one” side of a one-to-many relationship is a dimension, the “many” side a fact. A dimension must have a unique key. If a relationship dialog says that one of the columns must have unique values, neither side is unique, and the fix is to build a proper dimension (for example, a distinct list of keys) rather than to force the relationship.

Cardinality and filter direction

Filters travel along relationships. A slicer on Product narrows the Product table, the filter flows through the relationship to Sales, and the measure sums only the matching rows. The relationship settings control that flow:

Setting	Options	Default and exam position
Cardinality	Many to one, one to many, one to one, many to many	Many-to-one (dimension is the one side) is the common default
Cross filter direction	Single, Both	Single, filtering from the one side toward the many side
Active	One active relationship between any two tables	Others must be inactive

Single direction means filters work on the table where aggregation happens. The standard failure shows up as a count of dimension members that never changes when a slicer on another dimension is applied, because the filter cannot travel from the fact table back to the dimension. Both makes it travel, but Microsoft’s guidance is to minimize bi-directional relationships, because they can hurt query performance and produce confusing results. The documentation describes only three situations where they solve a requirement: special relationships (one-to-one relationships must be bi-directional, and a many-to-many between dimensions needs a bridging table with bi-directional filtering), slicers that show only options “with data”, and dimension-to-dimension analysis. For the last two, the recommended alternatives are a visual-level “is not blank” filter on the measure, or the DAX CROSSFILTER function inside a single measure.

Power BI also refuses a Both setting that would create ambiguity, which happens when two fact tables, such as Sales and Budget, share dimensions and form a loop.

Four design patterns to recognise

Snowflake dimensions. Product, Subcategory and Category in three normalised tables. Microsoft’s guidance is generally to combine them into one denormalised dimension, because it means fewer tables, shorter filter paths, and the ability to build a hierarchy across the levels. DP-600 phrases this as denormalising.
Role-playing dimensions. One Date table used for order date, ship date and delivery date. Only one relationship between two tables can be active; the others are inactive and used through USERELATIONSHIP in a measure. To filter by several date roles at once, create a separate date table for each role.
Many-to-many between dimensions. Salespeople who work in several regions. Use a bridging table (a factless fact table holding only the two keys) with one-to-many relationships to each dimension.
Degenerate dimension. An order number that you filter by but that has no attributes. It stays in the fact table instead of getting its own table, the allowed exception to keeping the two table types apart.

A worked check

You are given Sales (OrderID, ProductKey, CustomerKey, OrderDate, Amount), Product (ProductKey, Name, Category), and Customer (CustomerKey, Country). Create Product to Sales and Customer to Sales, both one-to-many, both single direction. A card visual with total Amount and slicers on Category and Country behaves correctly. The question “how many countries bought bikes?” fails: Category filters Sales, but Sales cannot filter Customer, so the country count ignores the product choice. Rather than set the relationship to Both for the whole model, write one measure, CALCULATE ( DISTINCTCOUNT ( Customer[Country] ), CROSSFILTER ( Customer[CustomerKey], Sales[CustomerKey], BOTH ) ), which is the pattern Microsoft’s bi-directional guidance shows. DAX in this measure is explained in DAX basics.

Which exams test this

Exam	How the topic appears	Where on this site
PL-300	Prepare the data (25–30%): create fact and dimension tables and the keys for their relationships. Model the data (25–30%): role-playing dimensions, relationship cardinality and cross-filter direction, a common date table, and reducing granularity for performance	Model the data explained
DP-600	Prepare data (45–50%): implement a star schema for a lakehouse or warehouse and denormalize data. Implement and manage semantic models (25–30%): implement a star schema for a semantic model and relationships such as bridge tables and many-to-many	Prepare data explained, Semantic models explained
DP-700	Ingest and transform data (30–35%): prepare data for loading into a dimensional model and denormalize data. Loading facts and dimensions, not Power BI relationships	Ingest and transform explained
Databricks Data Analyst Associate	Data Modeling with Databricks SQL (5%): apply star, snowflake and data vault schemas to analytical workloads and relate them to the medallion architecture. A small section	Databricks Data Analyst: analysing and securing
Databricks Data Engineer Professional	Data Modeling (5%, outline from October 9, 2026): design dimensional models for analytical workloads, with materialized views and Unity Catalog metric views. A small section	Ingestion, quality and modelling

How the questions are built

Which table is the fact table. Look for the table with many rows, numeric values and foreign keys.
Cardinality words. “Each product appears once in the product list but many times in sales” is one-to-many from Product. “Neither column is unique” points to a bridge or a new dimension, not a forced relationship.
The tempting Both. If an option makes the relationship bi-directional “to fix” a slicer or count, check whether a measure-level or visual-level alternative is offered. Those are usually preferred.
Duplicate relationships. Two relationships between the same two tables, one is inactive. Reaching the inactive one means a DAX measure, or a second copy of the dimension.
Flat table. A single wide table imported from a spreadsheet should be split into facts and dimensions using Power Query, which is covered in Power Query basics.

Question 1. A model has a Sales fact table and separate Date, Product and Customer tables. Sales contains OrderDate and ShipDate. Report authors build visuals by summarizing the Sales Amount column without writing measures, and they must filter by order month and ship month at the same time, for example orders placed in March that shipped in April. What should you do?

A. Make the Date to Sales relationship bi-directional
B. Keep one date table and one active relationship, and rely on USERELATIONSHIP for ship date
C. Create a second date table for ship date, each with an active relationship to Sales
D. Merge the Date columns into the Sales table

Show answer

Answer: C

Only one relationship between two tables can be active, and an inactive one is reachable only through a DAX measure with USERELATIONSHIP, which authors who only summarize columns cannot use, and which still filters by one date role at a time. Creating a second date table for the ship role gives each role its own active relationship, so both can filter at the same time. Bi-directional filtering does not help with date roles. Merging Date into Sales would repeat date attributes on every row and lose the dimension.

Question 2. A salesperson can work in several sales regions, and a region has several salespeople. You must let report users analyze sales by both dimensions without a direct many-to-many relationship. What should you add?

A. A single flat table combining salespeople and regions
B. A bridging table with salesperson and region keys, related one-to-many to each dimension
C. A calculated column that concatenates the two keys in the Sales table
D. No relationship, and a measure that ignores filters on both tables