Day 5: Data Modelling — Relationships

A data model is the collection of tables and the relationships between them that Power BI uses to answer questions across multiple tables. A good data model lets you slice sales data by customer attributes, product attributes, and time — even though these attributes live in separate tables. The data model sits between Power Query (where data is cleaned) and Report View (where visuals are built). A well-designed model makes DAX measures simpler and report performance faster.

A star schema organises data into one central fact table (containing measurable events — sales transactions, orders, calls) surrounded by dimension tables (containing descriptive attributes — customer, product, date, location). Relationships go from dimension tables to the fact table. Power BI's DAX engine is optimised for star schema — queries run faster, relationships are simpler, and measures are easier to write. The alternative (snowflake schema with normalised dimensions) is harder to model in Power BI and slower.

A fact table contains the measurable events or transactions of your business — sales orders, website visits, support tickets, payments. Each row is one event. Columns are typically keys (linking to dimensions) and numeric measures (revenue, quantity, duration). Fact tables are usually tall (many rows) and narrow (few columns). A dimension table contains the descriptive attributes of the entities in your fact table — customer name, product category, city, date details. Dimension tables are usually short (fewer rows) and wide (many attribute columns).

One-to-Many (1:*): most common — one row in the dimension table matches many rows in the fact table. Example: one customer can have many orders. Many-to-One (*:1): same relationship viewed from the fact table side. Many-to-Many (*:*): multiple rows in both tables can match — requires careful design, use a bridge table if possible. One-to-One (1:1): one row in each table matches exactly one in the other — often a sign the tables should be merged. In a star schema, all relationships should be 1:* from dimension to fact.

Cross-filter direction controls which way filters flow between related tables. Single direction: filters flow from the "one" side to the "many" side (dimension filters fact). This is the default and correct for most star schema models. Both directions (bidirectional): filters flow both ways — dimension filters fact AND fact filters dimension. Use bidirectionally sparingly — it can cause ambiguous filter paths, circular dependencies, and incorrect calculation results. A common symptom of incorrect cross-filter direction: a slicer on one table unexpectedly affects another table it should not filter.