In this post we are going to cover data modeling and relationships in Power BI: star schema, fact and dimension tables, grain, cardinality, filter direction, inactive relationships, many-to-many, and the date table. These are 7 real Power BI interview questions, and the last one is a scenario a manager loves to ask: the budget is at a different grain than the sales.
The data model is the part of Power BI that nobody sees, but everything depends on it. A good model makes the DAX short and the numbers right. A bad model makes every measure a workaround. That is why interviewers spend so much time here. If you can explain why a relationship points one way and not the other, you sound like someone who has built real models.
Every example below runs on a small sample data set, so you can rebuild it in Power BI Desktop and check each number yourself.
Part 4 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: Why do we build a star schema instead of one big flat table?
- Question 2: What is the difference between a fact table and a dimension table, and what is the grain?
- Question 3: What relationship cardinalities are there, what is cross-filter direction, and why should you avoid bidirectional filters?
- Question 4: What is an inactive relationship, what does USERELATIONSHIP do, and how do you handle Order Date and Ship Date?
- Question 5: How do you model a many-to-many relationship: bridge table or many-to-many cardinality? What is a limited relationship?
- Question 6: Why do you need a dedicated date table, what does Mark as date table do, and why turn off Auto date/time?
- Question 7: Scenario - the budget is by category and month, but sales are by product and day. How do you model it?
- The full SQL script
The short answer
Build a star schema: facts at a clear grain, dimensions around them, one-to-many, single-direction relationships. Use USERELATIONSHIP for role-playing dates, a bridge table for many-to-many, one marked date table, and relate every fact at its own grain.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales. This is our fact table: keys, a quantity and a unit price on every row.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
Question 1: Why do we build a star schema instead of one big flat table?
A star schema keeps the numbers in a fact table and the descriptions in dimension tables around it. In our sample, fact_sales sits in the middle, and date, product and customer sit around it.
Why is that better than one wide flat table? There are three reasons you should give:
- A smaller model. Product names and customer cities are stored once in their dimension, not repeated on every sales row, so the model compresses much better.
- Simpler DAX and correct filtering. Filters flow from the one side to the many side. A slicer on category or region just works, with no tricks inside the measures.
- Reuse. The same date dimension can filter sales, budget and inventory, so every report agrees on what a month is.
The pitfall to mention: a flat table looks easy on day one, but the moment a second fact arrives, like a budget, it falls apart. With a star schema, the budget is just another fact table that shares the same dimensions. We come back to that in Question 7.
Question 2: What is the difference between a fact table and a dimension table, and what is the grain?
A fact table stores the events you measure, like sales. It has numeric columns and foreign keys. It is long and narrow, and it grows every day. The fact_sales table above is our fact table.
A dimension table describes the things you slice by: products, customers, dates. It is short and wide, with one row per member and one unique key. Here is our product dimension.
| product_key | product | category |
|---|---|---|
| 1 | Laptop Stand | Accessories |
| 2 | USB-C Cable | Accessories |
| 3 | Monitor | Electronics |
| 4 | Keyboard | Electronics |
| 5 | Desk Lamp | Home |
| Fact | Dimension | |
|---|---|---|
| STORES | events, numbers | descriptions |
| SHAPE | long, narrow | short, wide |
| KEYS | foreign keys | one unique key |
The grain is what one row of the fact table means. In our sample it is one row per sale line: one product, for one customer, on one order date. Our fact table has 12 of them.
In an interview, say that you define the grain first, before you pick a single column. Then keep it consistent. If you mix daily rows and monthly totals in one fact table, every sum is wrong, because the monthly rows get added on top of the daily ones.
Question 3: What relationship cardinalities are there, what is cross-filter direction, and why should you avoid bidirectional filters?
Cardinality says how the keys match on the two sides of a relationship.
| Typical use | |
|---|---|
| ONE-TO-MANY | dimension to fact (the normal case) |
| ONE-TO-ONE | rare: usually merge the tables |
| MANY-TO-MANY | neither side unique |
One-to-many is the normal case: one product, many sales rows. One-to-one is rare, and usually means two tables that should be merged. Many-to-many means neither side is unique.
Cross-filter direction says which way filters flow. Single means from the one side to the many side. So when a visual groups by category, dim_product filters fact_sales. Here is that relationship doing its job.
| category | total_sales |
|---|---|
| Electronics | 1450.00 |
| Accessories | 290.00 |
| Home | 180.00 |
Check this result yourself with SQL
Product is the one side, Sales the many side: the category filter flows from Product to Sales.
SELECT p.category, SUM(f.qty * f.unit_price) AS total_sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY total_sales DESC;Electronics is 1450.00, Accessories 290.00 and Home 180.00. The relationship carries the category filter from the product table to the sales rows.
Both, or bidirectional, lets filters flow back the other way too. Avoid it as a default. It can create ambiguous filter paths, it costs performance, and it gives unexpected results, because one table can now quietly filter another.
If one measure really needs a filter to flow the other way, use CROSSFILTER inside that measure instead of changing the relationship for the whole model.
Question 4: What is an inactive relationship, what does USERELATIONSHIP do, and how do you handle Order Date and Ship Date?
Between two tables, only one relationship path can be active. Imagine our sales table also had a ship date. Order Date is the active relationship to the date table, the solid line in the model view. Ship Date has to be inactive, the dashed line.
An inactive relationship does nothing on its own. You switch it on inside a measure with USERELATIONSHIP, inside CALCULATE. Here is a measure that groups the same sales by the date they shipped.
Sales by Ship Date =
CALCULATE (
[Total Sales],
USERELATIONSHIP ( Sales[Ship Date], 'Date'[Date] )
)This is called a role-playing dimension: one date table playing two roles, order date and ship date.
The alternative is a separate copy of the date table for each role, an Order Date table and a Ship Date table. So which one do you pick?
- One date table, inactive relationship, USERELATIONSHIP - fine when only a few measures need the second date.
- A copy per role - pick this when users need to slice by both dates at once, or when you would otherwise write a USERELATIONSHIP version of every measure.
Question 5: How do you model a many-to-many relationship: bridge table or many-to-many cardinality? What is a limited relationship?
The classic example is a bank. A customer can own many accounts, and an account can have many owners. The clean answer is a bridge table, with one row per customer and account pair.
Here is how you wire it up:
- Customer relates one-to-many to the bridge.
- Account relates one-to-many to the bridge.
- The Balance fact relates to Account.
- Set the bridge to Account relationship to filter both ways, so a customer filter reaches the balances. Then hide the bridge.
Power BI also has a many-to-many cardinality setting, which joins two tables on a column that is not unique on either side. It is handy for facts at a higher grain. But it creates a limited relationship.
| Bridge table | Many-to-many cardinality | |
|---|---|---|
| RELATIONSHIPS | regular, one-to-many | limited |
| UNMATCHED ROWS | shown under Blank | dropped (inner join) |
| RELATED | works | not available |
| USE FOR | customers and accounts | facts at a higher grain |
Limited means two things you should say out loud: rows with no match are dropped instead of showing under Blank, and RELATED does not work across it. So use a bridge for a real many-to-many between entities, and many-to-many cardinality only when you understand that behavior.
Question 6: Why do you need a dedicated date table, what does Mark as date table do, and why turn off Auto date/time?
Time intelligence, like year to date or same period last year, needs a proper date table: one row per day, no gaps, no duplicates, covering every date in your facts.
Let's check that our dim_date qualifies.
| days | first_date | last_date | distinct_dates | days_in_range |
|---|---|---|---|---|
| 730 | 2025-01-01 | 2026-12-31 | 730 | 730 |
Check this result yourself with SQL
730 distinct dates in a 730-day range: unique, contiguous, no blanks, so it can be marked as a date table.
SELECT COUNT(*) AS days,
MIN(date) AS first_date,
MAX(date) AS last_date,
COUNT(DISTINCT date) AS distinct_dates,
MAX(date) - MIN(date) + 1 AS days_in_range
FROM dim_date;It has 730 rows, from 2025-01-01 to 2026-12-31. There are 730 distinct dates in a 730 day range, so there are no duplicates and no gaps.
If your source does not have a date table, build one in DAX with CALENDAR or CALENDARAUTO and add year, quarter and month columns. Here is one for the same two years.
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Quarter", "Q" & QUARTER ( [Date] ),
"Month No", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" )
)Then use Mark as date table, which the classic time intelligence functions rely on. Power BI checks that the date column is unique, has no blanks and has no gaps.
Finally, turn off Auto date/time in Options. It creates a hidden date table for every date column, which makes the model bigger and the refresh longer, and it can't filter across tables. One shared date table does the job properly and filters every fact.
Question 7: Scenario - the budget is by category and month, but sales are by product and day. How do you model it?
A manager asks: the budget is by category and month, but sales are by product and day. How do you model it?
The answer: don't force the budget down to product or day. Keep it as its own fact table, at its own grain, and relate it to the dimensions at that grain.
- Time: store the first day of each month in the budget, and relate it to the date table.
- Category: add a small category dimension that filters both the product table and the budget. Microsoft's guidance also allows a many-to-many relationship on category, filtering in a single direction.
- Compare: one visual by category now shows budget and actual side by side.
In our sample, the 2026 budget is 300 a month for Electronics, 50 for Accessories and 40 for Home, for January to March. Here it is next to the actual sales.
| category | budget | actual | variance |
|---|---|---|---|
| Electronics | 900 | 850.00 | -50.00 |
| Accessories | 150 | 160.00 | 10.00 |
| Home | 120 | 120.00 | 0.00 |
Check this result yourself with SQL
Budget stays at its own grain (first day of the month, category) and meets sales only through shared dimensions.
WITH budget (month_start, category, amount) AS (
VALUES (DATE '2026-01-01', 'Electronics', 300), (DATE '2026-02-01', 'Electronics', 300), (DATE '2026-03-01', 'Electronics', 300),
(DATE '2026-01-01', 'Accessories', 50), (DATE '2026-02-01', 'Accessories', 50), (DATE '2026-03-01', 'Accessories', 50),
(DATE '2026-01-01', 'Home', 40), (DATE '2026-02-01', 'Home', 40), (DATE '2026-03-01', 'Home', 40)),
bud AS (
SELECT b.category, SUM(b.amount) AS budget
FROM budget b JOIN dim_date d ON d.date = b.month_start
WHERE d.year = 2026
GROUP BY b.category),
act AS (
SELECT p.category, SUM(f.qty * f.unit_price) AS actual
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d ON d.date = f.order_date
WHERE d.year = 2026
GROUP BY p.category)
SELECT b.category, b.budget, a.actual, a.actual - b.budget AS variance
FROM bud b JOIN act a ON a.category = b.category
ORDER BY b.budget DESC;Electronics had a budget of 900 and sold 850.00, so it is 50.00 under. Accessories sold 160.00 against 150, 10.00 over. Home is exactly on budget at 120.
The pitfall: put the budget next to a single product or a single day, and the number repeats and misleads. So the Budget measure checks ISFILTERED on the lower-level columns and returns blank below its grain.
Budget =
IF (
NOT ISFILTERED ( 'Product'[Product] )
&& NOT ISFILTERED ( 'Date'[Date] ),
SUM ( Budget[Amount] )
)In the interview, say "relate each fact at its own grain" before you talk about any DAX. That one sentence tells the manager you know how to model, not just how to write formulas.
Key points to remember
- Star schema: facts in the middle, dimensions around
- Define the grain first and keep it consistent
- One-to-many, single direction; bidirectional only when needed
- USERELATIONSHIP or copies for role-playing dates
- Bridge tables, one marked date table, facts at their own grain
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind every result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #4 - Data modeling and relationships
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q04; SET search_path TO pbi_q04;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Sales by category through the one-to-many Product to Sales relationship
SELECT p.category, SUM(f.qty * f.unit_price) AS total_sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY total_sales DESC;
-- Step 2: Is dim_date a valid date table? One row per day, no gaps
SELECT COUNT(*) AS days,
MIN(date) AS first_date,
MAX(date) AS last_date,
COUNT(DISTINCT date) AS distinct_dates,
MAX(date) - MIN(date) + 1 AS days_in_range
FROM dim_date;
-- Step 3: Budget (category and month grain) vs actual sales (product and day grain), 2026
WITH budget (month_start, category, amount) AS (
VALUES (DATE '2026-01-01', 'Electronics', 300), (DATE '2026-02-01', 'Electronics', 300), (DATE '2026-03-01', 'Electronics', 300),
(DATE '2026-01-01', 'Accessories', 50), (DATE '2026-02-01', 'Accessories', 50), (DATE '2026-03-01', 'Accessories', 50),
(DATE '2026-01-01', 'Home', 40), (DATE '2026-02-01', 'Home', 40), (DATE '2026-03-01', 'Home', 40)),
bud AS (
SELECT b.category, SUM(b.amount) AS budget
FROM budget b JOIN dim_date d ON d.date = b.month_start
WHERE d.year = 2026
GROUP BY b.category),
act AS (
SELECT p.category, SUM(f.qty * f.unit_price) AS actual
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d ON d.date = f.order_date
WHERE d.year = 2026
GROUP BY p.category)
SELECT b.category, b.budget, a.actual, a.actual - b.budget AS variance
FROM bud b JOIN act a ON a.category = b.category
ORDER BY b.budget DESC;
Part 5: DAX Fundamentals and Filter Context
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.