Webbo3 Data Analysis Bootcamp · Power BI Module · Lesson 3

Data Modeling in Power BI: Star and Snowflake Schemas, Relationships, Cardinality, and Row-Level Security

A foundational lesson on designing data models in Power BI, managing table relationships, understanding cardinality and cross-filter direction, using RELATED and RELATEDTABLE in DAX, and implementing basic row-level security.

In the previous lessons you learned to import data, clean it in Power Query, and build basic visuals. But a Power BI report with beautiful charts built on a poorly designed model is like a house with marble countertops on a crumbling foundation. It looks impressive until someone leans on it. Data modeling is the foundation. It determines whether your measures calculate correctly, whether your slicers filter what you expect, whether your report refreshes in seconds or minutes, and whether your data is secure. This lesson covers the core modeling concepts every Power BI analyst must master: dimensional schemas, relationship design, cardinality, filter direction, DAX context functions, and the basics of row-level security. These are not advanced topics reserved for senior architects. They are the baseline for anyone who wants to build reports that do not break when the dataset grows.

1. Star Schema versus Snowflake Schema

Power BI's engine, VertiPaq, is optimized for a specific data structure: the star schema. Understanding why this is the preferred design, and when a snowflake schema might be acceptable, is the first step in building models that perform well and behave predictably.

What is a star schema? A star schema consists of one central fact table surrounded by dimension tables. The fact table contains the quantitative data you want to analyze: sales amounts, quantities, counts, and the foreign keys that link to dimensions. The dimension tables contain the descriptive attributes that provide context: product names, customer locations, dates, and categories. Each dimension table connects directly to the fact table in a one-to-many relationship. The fact table sits at the center, and the dimensions radiate outward like points on a star. This is the design that Power BI's VertiPaq engine compresses most efficiently and queries fastest.

The advantages of a star schema are speed and simplicity. Because dimension tables are denormalized, meaning all attributes for a single entity live in one table, there are fewer joins for Power BI to process when a user interacts with a visual. A filter on a product category flows directly from the Product dimension to the fact table in one hop. DAX measures are simpler to write because the filter context travels a straight path. VertiPaq's columnar compression also works best when repeated values are stored together, which is exactly what denormalized dimension tables provide. The trade-off is storage: category names and other attributes are repeated across many product rows, which increases raw data size, though compression often offsets this.

What is a snowflake schema? A snowflake schema is a variation where dimension tables are normalized into sub-dimensions. Instead of one Product dimension containing category, subcategory, and product attributes, you have a chain: Product connects to Subcategory, which connects to Category. The structure branches out like a snowflake. This design reduces data redundancy because each category name is stored once in the Category table, not repeated on every product row. It is useful when dimensions have complex hierarchies or when multiple fact tables at different granularities need to share the same dimension structure.

The disadvantages are performance and complexity. Every additional relationship hop adds processing overhead. A filter on Category must travel through Subcategory to Product to the fact table, which is slower than the direct hop in a star schema. DAX becomes more complex because filter context must traverse intermediate tables, and you may need functions like CROSSFILTER or USERELATIONSHIP to manage unexpected behavior. For most Power BI projects, Microsoft and industry experts consistently recommend starting with a star schema and only deviating to snowflake when specific requirements demand it.

How to build a star schema in Power BI. If your source data is normalized, as it often is in relational databases, you must denormalize it in Power Query before loading into the model. Use the Merge Queries feature to join dimension attributes into a single table. For example, if you have separate Product, Subcategory, and Category tables, merge Subcategory into Product, then merge Category into the result. Remove the now-redundant Subcategory and Category tables from the model, keeping only the flattened Product dimension. The foreign key columns in your fact table should link directly to this single Product dimension. Repeat this process for every dimension: Customer, Date, Location, and so on. The result is a clean star with the fact table at the center and one table per dimension surrounding it.

2. Managing Relationships in Model View

Relationships in Power BI define how tables interact. Without relationships, filters in one table do not affect another, and DAX measures that span tables return blank or incorrect results. The Model View in Power BI Desktop is where you create, edit, and inspect these relationships visually.

Creating relationships. Go to the Model view tab in the left sidebar of Power BI Desktop. You will see every table in your model represented as a box with its column names. To create a relationship, drag a column from one table and drop it onto the matching column in another table. Power BI draws a line between them and attempts to detect the cardinality and direction automatically. For example, dragging ProductID from a Product dimension table to ProductID in a Sales fact table creates a one-to-many relationship from Product to Sales, because ProductID is unique in the Product table and repeats in the Sales table.

Editing relationships. Double-click any relationship line to open the Edit Relationship dialog. Here you can verify or change the cardinality, the cross-filter direction, whether the relationship is active, and whether to assume referential integrity. You can also set the relationship to use a single column or multiple columns as the key. For most star schema designs, relationships should be one-to-many, single direction, and active. If Power BI detects the wrong cardinality, usually because of duplicate values in what should be a unique key column, you must fix the data quality issue in Power Query rather than forcing the wrong cardinality in the model.

Active versus inactive relationships. A table can have multiple relationships to another table, but only one can be active at a time. The active relationship is the one Power BI uses by default when evaluating filters and DAX measures. Inactive relationships are used in specific DAX calculations via the USERELATIONSHIP function. A common example is a Date table related to a Sales table on both OrderDate and ShipDate. The OrderDate relationship is active because most analysis is based on order date. The ShipDate relationship is inactive, but you can write a measure that specifically uses it for delivery analysis by wrapping your calculation in USERELATIONSHIP.

Avoiding circular relationships. Power BI does not allow circular relationships, where Table A filters Table B, Table B filters Table C, and Table C filters Table A. If you accidentally create such a loop, Power BI will display an error and refuse to commit the relationship. The fix is to remove or redesign one of the relationships, often by creating a bridge table or by merging tables in Power Query to eliminate the circular dependency. A clean star schema naturally avoids circular relationships because all dimensions filter the fact table directly, and dimensions do not filter each other.

3. Cardinality: One-to-One, One-to-Many, Many-to-Many

Cardinality describes how many rows in one table relate to how many rows in another. It is the most important property of a relationship because it determines how filters propagate and how aggregations behave. Power BI supports three cardinality types.

One-to-many (1:M) and many-to-one (M:1). These are the same relationship viewed from opposite directions. In a star schema, every relationship between a dimension and the fact table is one-to-many. The dimension table is on the one side because its key column contains unique values. The fact table is on the many side because its foreign key column contains repeated values. For example, one product in the Product table can appear in many sales transactions in the Sales table. When you create the relationship from Product to Sales, Power BI sets the cardinality to one-to-many. If you create it from Sales to Product, it shows as many-to-one. The underlying logic is identical. One-to-many is the most common and most performant cardinality type in Power BI. It enables simple DAX, predictable filtering, and efficient compression.

One-to-one (1:1). A one-to-one relationship means both columns contain unique values, so each row in Table A matches exactly one row in Table B. This cardinality is uncommon and often indicates a suboptimal model design. If two tables have the same granularity and the same key, you should usually merge them into a single table in Power Query rather than maintaining a separate one-to-one relationship. Separate tables with one-to-one relationships create unnecessary complexity, increase model size, and can slow down refresh. The only valid use case is when two large tables must remain separate for organizational reasons, but even then, consider whether a single table or a star schema redesign is better.

Many-to-many (M:M). Many-to-many means both columns can contain duplicate values. This cardinality is infrequently used and should be avoided when possible. It is typically needed only for complex scenarios such as relating a fact table at product category level to a dimension table at product level, or when a bridge table is required to resolve a many-to-many relationship between two dimensions. For example, a Sales table might have one row per sales rep per month, while a Targets table has one row per region per month. Relating these directly requires a many-to-many relationship, or a bridge table that creates a proper star schema. Many-to-many relationships increase model complexity, slow query performance, and can produce ambiguous results if not carefully designed. If you find yourself needing many-to-many, pause and ask whether the data can be restructured in Power Query to avoid it.

Setting cardinality correctly. In the Model view, double-click a relationship line. The Edit Relationship dialog shows the cardinality options. Power BI usually detects the correct type automatically based on the data, but you should always verify it. If the one side contains duplicates that Power BI did not detect, perhaps because of trailing spaces or case differences, the relationship will behave unpredictably. Clean your key columns in Power Query: trim spaces, standardize case, remove duplicates, and ensure data types match exactly before creating relationships. A relationship between a text key and a numeric key will fail or behave incorrectly even if the values look the same.

4. Cross-Filter Direction: Single versus Both

Cross-filter direction determines how filters flow between related tables. It is not a cosmetic setting. It fundamentally changes which visuals interact with which other visuals, and whether your DAX measures return the values you expect.

Single direction. In a single-direction relationship, filters flow from the one side to the many side. If you select a product category in a slicer, the Sales table filters to show only sales for that category. But if you select a sales amount in a visual, the Product table does not filter to show only products with that sales figure. The filter goes one way: dimension to fact. This is the default and the recommended setting for almost all relationships in a star schema. Single-direction filtering is predictable, performant, and prevents the ambiguous filter paths that can occur when filters flow in multiple directions simultaneously.

Both directions (bi-directional). In a both-direction relationship, filters flow both ways. Selecting a product category filters the Sales table, and selecting a sales transaction also filters the Product table to show only the product in that transaction. This enables more interactive visuals, such as a table of customers that dynamically filters based on the products they bought, even though the relationship direction is technically from Product to Sales. However, bi-directional filtering comes with significant costs. It can slow down report performance because every visual interaction must evaluate filter propagation in both directions. It can create ambiguous filter paths where Power BI cannot determine which route to take, resulting in errors or incorrect results. It can also cause unexpected totals in visuals because filter context from one table propagates backward through the model in ways that are hard to trace.

When to use both directions. Use bi-directional filtering only when necessary. Common valid use cases include dynamic security filtering, where a user security table must filter a dimension that then filters a fact table, and specific interactive scenarios where a slicer on a fact table must filter a dimension. Even in these cases, consider whether the same result can be achieved with a DAX measure using CROSSFILTER to enable bi-directional filtering for a single calculation, rather than setting the entire relationship to both directions permanently. The CROSSFILTER function allows you to change the filter direction only within the context of one measure, leaving the model relationship as single direction for everything else. This is the safest approach when you need bi-directional behavior in one specific visual but not globally.

Configuring cross-filter direction. In the Model view, double-click a relationship line. In the Edit Relationship dialog, the Cross Filter Direction dropdown offers Single and Both. For one-to-many relationships, Single is the default. Changing it to Both enables bi-directional filtering. If you enable Both, an additional checkbox appears: Apply security filter in both directions. This is specifically for row-level security scenarios where a security table must filter a dimension, and that dimension must then filter the fact table. Only check this box if you are implementing RLS and need the security filter to propagate through this relationship. For standard reports, leave it unchecked.

5. RELATED and RELATEDTABLE in DAX Context

DAX has two functions that allow you to traverse relationships within calculations: RELATED and RELATEDTABLE. They are not interchangeable. They work in opposite directions and in different evaluation contexts. Using the wrong one produces a blank result or an error.

RELATED: looking up the one side from the many side. RELATED is used in a calculated column on the many side of a relationship to fetch a value from the one side. It works in row context, which is the context of a single row in a table. For example, if you have a Sales table with a ProductID column, and you want to add a calculated column that shows the product category for each sale, you write:

Product Category = RELATED('Product'[Category])

For each row in the Sales table, RELATED follows the relationship from Sales to Product, finds the matching ProductID in the Product table, and returns the Category value from that row. If the relationship does not exist, or if the current table is not on the many side of a relationship, RELATED throws an error. It can only traverse one-to-many relationships from many to one, not the reverse. You can chain multiple RELATED calls if there are multiple hops, but each hop must follow the many-to-one direction.

RELATEDTABLE: looking up the many side from the one side. RELATEDTABLE is the inverse. It is used in a calculated column or measure on the one side of a relationship to return a table of all related rows from the many side. It also works in row context. For example, if you want to add a calculated column to the Product table that counts how many sales transactions exist for each product, you write:

Sales Count = COUNTROWS(RELATEDTABLE(Sales))

For each row in the Product table, RELATEDTABLE returns the subset of the Sales table where ProductID matches the current product. COUNTROWS then counts how many rows are in that subset. RELATEDTABLE returns a table, not a scalar value, so you must wrap it in an aggregation function like COUNTROWS, SUMX, or AVERAGEX to produce a single value for the calculated column. In a measure, RELATEDTABLE is often used within an iterator function to perform row-by-row calculations over the related rows.

Key distinction. Use RELATED when you are on the many side and need one value from the one side. Use RELATEDTABLE when you are on the one side and need a set of rows from the many side. If you try to use RELATED from the one side, or RELATEDTABLE from the many side, DAX will not find a valid relationship path and will return an error. Always check your position in the relationship before choosing which function to use.

6. Row-Level Security Basics

Row-level security, or RLS, restricts data access at the row level based on the identity of the user viewing the report. It is not a visual filter that users can override. It is a data model filter applied automatically before any visual or DAX measure is evaluated. If a user is not authorized to see certain rows, those rows simply do not exist in the dataset for that user. RLS is essential for multi-tenant reports, confidential data, and compliance requirements.

Static RLS with DAX filters. The simplest form of RLS uses a DAX expression that filters a table based on a fixed condition. In Power BI Desktop, go to Modeling → Manage Roles → Create. Name your role, for example "Lagos Only." Select the table you want to filter, for example a Sales table with a Region column. Enter the DAX filter expression:

[Region] = "Lagos"

Users assigned to this role will see only rows where Region equals Lagos. All other rows are hidden from them entirely. The filter propagates through relationships automatically. If the Sales table is related to a Product table, and the user filters a visual by Product, they will see only products that have sales in Lagos, because the Lagos filter on Sales has already eliminated all other transactions and their related products. This is the power of RLS: it respects the model relationships and applies before any user interaction.

Dynamic RLS with USERNAME or USERPRINCIPALNAME. Static roles are manageable for a few fixed segments, but they do not scale. If you have one hundred sales managers and each should see only their own region, creating one hundred static roles is impractical. Dynamic RLS solves this by using the viewing user's identity. Create a security table, for example UserRoles, with two columns: Email and Region. Each row maps a user's email to the region they are allowed to see. Create a relationship from UserRoles to your main table on the Region column. Then create a role with this filter expression on the UserRoles table:

[Email] = USERNAME()

USERNAME returns the email address of the currently logged-in user in Power BI Service. USERPRINCIPALNAME returns the user principal name, which is similar but may include domain information. When a user opens the report, Power BI evaluates this filter first. It keeps only the rows in UserRoles where Email matches the current user, which leaves only the regions that user is authorized to see. The relationship from UserRoles to the main table then propagates that region filter, hiding all other data. One role definition serves all users. You simply maintain the UserRoles table as users join or change regions.

Testing RLS in Power BI Desktop. After defining roles, you must test them before publishing. Go to Modeling → View As, select a role, and click OK. The entire report canvas refreshes to show only the data visible to that role. Navigate through pages, slicers, and visuals to confirm that no unauthorized data leaks through. Pay special attention to measures that use ALL or REMOVEFILTERS, because these functions intentionally remove filters and can accidentally expose data that RLS was supposed to hide. If a measure uses ALL('Sales'), it bypasses the RLS filter on Sales and shows the entire table. This is a common security vulnerability. Always audit your measures for ALL and REMOVEFILTERS when RLS is active.

Applying RLS in Power BI Service. After publishing your report, go to the dataset settings in Power BI Service. Under Security, add members to each role by email address or security group. Users who are not assigned to any role will see no data at all, unless you have configured a default role or disabled RLS. Test again in the Service environment by using the Test as role feature. Document your RLS design for future maintainers, because a poorly documented security model becomes a liability when the original developer leaves the organization.

Using AI to Move Faster in Power BI Data Modeling

Data modeling requires judgment and domain knowledge that AI cannot fully replace. But AI can dramatically accelerate the mechanical work of schema design, relationship detection, DAX writing, and security setup. Here is how to use it without surrendering your understanding.

1. Use AI to suggest a star schema from your raw tables.
Paste a list of your source tables and their columns into Copilot or ChatGPT and ask: "Suggest a star schema design for these tables. Identify which should be the fact table, which should be dimensions, and which columns should be merged or removed. Explain your reasoning." AI will likely identify the table with the most numeric measures and foreign keys as the fact table, and the tables with descriptive attributes as dimensions. It may suggest merging normalized sub-tables into single dimensions. Use this as a starting blueprint, then verify against your actual business requirements. AI is good at pattern recognition but may miss domain-specific nuances, like why a certain table must remain separate for regulatory reasons.

2. Generate DAX for RELATED and RELATEDTABLE with natural language.
Instead of memorizing the exact syntax and direction, describe what you need: "I am in the Sales table and need a calculated column that shows the customer city from the Customer table. Write the DAX." AI will produce =RELATED('Customer'[City]). Or: "I am in the Product table and need a calculated column that counts how many sales each product has. Write the DAX." AI will produce =COUNTROWS(RELATEDTABLE(Sales)). Verify the direction: if you are on the many side and AI gives you RELATEDTABLE, or vice versa, correct it. This is where your understanding of cardinality matters more than AI's pattern matching.

3. Debug relationship errors with AI.
If Power BI shows a circular relationship error or ambiguous filter path warning, paste the error message and your model description into an AI assistant: "Power BI says there is an ambiguous filter path between Customer, Sales, and Product. Here are my relationships. How do I fix this?" AI can suggest removing a redundant relationship, creating a bridge table, or changing a cross-filter direction. Test the suggestion in a copy of your model before applying it to your production file. Relationship errors are often context-specific, and AI suggestions are educated guesses, not guaranteed fixes.

4. Draft RLS DAX expressions with AI assistance.
Dynamic RLS with USERNAME and security tables can be tricky to write correctly. Ask AI: "Write a dynamic RLS filter for a UserRoles table where Email matches the current user and Region filters a Sales table through a relationship. Include the DAX for the role and explain how to test it." AI will generate the [Email] = USERNAME() expression and explain the View As testing workflow. Again, verify that the column names match your actual model exactly. A mismatch between Email and UserEmail will cause the role to filter out all rows, leaving the user with a blank report.

5. Audit your model for performance and security before publishing.
Before deploying a report, ask AI: "Review this Power BI model design for common issues: bi-directional relationships that could be single, many-to-many relationships that could be redesigned, missing RLS on sensitive tables, and measures that use ALL which might bypass RLS." AI will flag potential problems based on best practices. Use this as a checklist, not a guarantee. Open Model View, inspect each relationship, test each role, and verify each measure manually. AI accelerates the audit process, but the final responsibility for a secure, performant model is yours.

A habit worth building from this lesson onward: every time you import a new dataset into Power BI, sketch the intended star schema on paper or in a note before touching the model. Then use AI to validate your design, suggest improvements, and generate the DAX you will need. This workflow, plan first, then automate, then verify, prevents the common trap of importing data and randomly dragging relationship lines until something works. A deliberate model design is the difference between a report that scales and one that collapses under its own weight.

Next lesson: DAX fundamentals, calculated columns, measures, and basic aggregation functions.