25 Data Modelling questions with answers for power bi roles — 9 easy, 11 medium, 5 hard.
About Data Modelling
A good model makes DAX simple and reports fast. This topic covers star schema, fact and dimension tables, grain, relationship cardinality, cross-filter direction, active and inactive relationships, role-playing dimensions, date tables, fiscal calendars, bridge tables for many-to-many, slowly changing dimensions and surrogate keys, all common modelling interview questions.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is a star schema and why is it recommended for Power BI?
A star schema has central fact tables holding numeric events, such as sales lines, linked to dimension tables describing them, such as Date, Product, Customer and Store. Power BI's engine and DAX are optimised for this shape: filters flow cleanly from dimensions to facts, measures are simpler, models compress better and reports run faster. Flat single tables or snowflakes usually need more complex DAX.
02
What is the difference between fact and dimension tables?
Fact tables store measurable events at a defined grain, such as one row per order line, with numeric columns like quantity and amount and foreign keys to dimensions. Dimension tables store descriptive attributes used for filtering and grouping, such as product name, category, region or calendar month, with one row per key. Facts are usually long and narrow; dimensions are shorter and wider.
Cardinality describes how keys match between two tables. One-to-many is the normal type, from a dimension's unique key to a fact table's repeating key. One-to-one links tables with unique keys on both sides and often means they should be merged. Many-to-many allows duplicates on both sides; it is supported but can produce confusing totals, so a bridge table is often clearer.
A date table gives one row per day with columns like year, quarter, month, week and fiscal periods, so all facts can be analysed on a common calendar. Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a contiguous date column, and the table should be marked as a date table. It also avoids the many hidden auto date/time tables that bloat the model.
05
A business user asks to merge customer and sales into one table to make it easier. How do you respond?
I would explain that a star schema with separate Customer and Sales tables is easier for them in practice: slicers are cleaner, totals are correct and reports run faster. To keep it user-friendly I would hide key columns, organise measures in display folders and name fields in plain language. I would show them the field list so they see simplicity on the surface.
Date =
ADDCOLUMNS(
CALENDAR(DATE(2022, 1, 1), DATE(2026, 12, 31)),
"Year", YEAR([Date]),
"Month No", MONTH([Date]),
"Month", FORMAT([Date], "MMM"),
"Quarter", "Q" & QUARTER([Date]),
"Weekday", FORMAT([Date], "ddd")
)
Then mark it as a date table and sort Month by Month No.
In Table view or Model view, select the date table, choose Table tools, Mark as date table, and select the date column, which must be unique, contiguous and without blanks. This tells Power BI to use it for time intelligence and disables the auto date hierarchy for that table. Also turn off Auto date/time under Options, Data Load to avoid hidden date tables for every date column.
By default month names sort alphabetically, April first. In the Date table add a Month Number column, select the Month name column, then choose Column tools, Sort by column, Month Number. The same applies to weekday names and fiscal months. The sort column must have one value per name value, otherwise Power BI shows an error.
09
How do you explain a data model to a non-technical stakeholder?
I use a simple analogy: the fact table is the list of transactions, and dimensions are the labels we use to slice them, like date, product and region. I show the model diagram with only main tables and explain that slicers work through these links. Then I demonstrate one visual changing when a slicer changes, which makes the idea concrete quickly.
10
What is cross-filter direction and why is bi-directional filtering risky?
Cross-filter direction controls which way filters travel across a relationship. Single direction, from the one side to the many side, is the default and safest. Bi-directional lets the fact table filter the dimension too, which can be useful for slicers showing only relevant values, but it can cause ambiguous paths, unexpected results and slower queries. Prefer single direction and use CROSSFILTER inside specific measures when needed.
Only one active relationship can exist between two tables along a filter path; additional relationships become inactive and appear as dashed lines. For example, Sales may have OrderDate and ShipDate both linked to Date, with OrderDate active. An inactive relationship is used only inside a measure with USERELATIONSHIP, such as CALCULATE([Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])).
12
What is a role-playing dimension and how do you handle it?
A role-playing dimension is one dimension used in several roles, such as Date acting as Order Date, Ship Date and Delivery Date. Options are one Date table with one active and other inactive relationships used through USERELATIONSHIP in measures, or separate copies of the date table for each role so users can slice by each date independently. The second is easier for users but increases model size slightly.
13
What is a snowflake schema and should you avoid it in Power BI?
A snowflake schema normalises dimensions into several related tables, such as Product linked to Subcategory linked to Category. It saves little space in Power BI because columnar compression already handles repetition, and it adds relationships, longer filter paths and a more confusing field list. Usually it is better to flatten these into one Product dimension in Power Query, keeping the model a clean star.
14
What is a calculated table and when would you use one?
A calculated table is created with a DAX expression and stored in the model, recalculated at refresh. Examples are a date table using CALENDAR, a disconnected parameter table for what-if analysis, or a summary table. It is handy but adds memory, and it cannot use Power Query folding. For most shaping tasks, creating tables in the source or Power Query is preferred.
15
Your model has one wide flat table with 80 columns from an Excel export. How would you remodel it?
I would identify the grain, such as one row per order line, and split descriptive attributes into dimension tables like Customer, Product, Store and Date using Reference queries and Remove Duplicates on keys. The fact table keeps keys and numeric columns only. Then create one-to-many single-direction relationships. This reduces size, simplifies DAX and makes slicers faster and more reliable.
16
A slicer on Product Category does not filter the Targets visual. What do you check?
I would check the relationship path in model view: whether Targets is related to the Product or Category dimension at all, whether the relationship is active, and its cross-filter direction. If Targets are at category level, it needs a relationship to a Category table that also filters Product. Also check whether visual interactions were turned off for that slicer and whether the measure uses ALL or REMOVEFILTERS.
17
Users want to analyse sales by both order date and delivery date in the same report. What do you do?
I would keep the Date table related to OrderDate as the active relationship and add an inactive relationship to DeliveryDate. Then create measures like Sales by Delivery Date using USERELATIONSHIP. If users need separate slicers for both dates at the same time, I would create a second Delivery Date table as a role-playing copy related actively to the delivery column.
18
How do you create a fiscal year column starting in April?
Fiscal Year =
VAR m = MONTH('Date'[Date])
VAR y = YEAR('Date'[Date])
RETURN
"FY " & IF(m >= 4, y, y - 1) & "-" & RIGHT(IF(m >= 4, y + 1, y), 2)
For April 2026 this returns FY 2026-27, which matches the Indian financial year. Add a fiscal month number column for sorting too.
Situation: a finance report took 40 seconds per page with a 2 GB file built on joined flat tables. Task: improve speed. Action: I split it into a star schema with Date, Account, Cost Centre and fact tables, removed unused columns, turned off auto date/time and replaced bi-directional filters with single direction. Result: model size dropped to 450 MB and pages loaded in under 4 seconds.
20
What is the grain of a fact table and why does it matter?
Grain is the level of detail of one fact row, such as one row per order line per day. It must be defined clearly before building measures, because mixing grains in one table, for example daily sales and monthly targets, causes double counting and wrong totals. Facts at different grains should be separate tables related to dimensions at the matching level.
21
How do you handle a many-to-many relationship between sales and budget at different grains?
Budgets are often at month and category level while sales are daily and per product. Instead of a direct many-to-many link, relate both fact tables to shared dimensions at the right level: Date and a Category dimension. Budget relates to Category and to Date through a month-level key or the first day of month. Measures then compare them, and visuals should use only the shared grain.
22
What are slowly changing dimensions and how do they affect Power BI models?
Slowly changing dimensions track attribute changes over time. Type 1 overwrites the old value, so history shows the current attribute. Type 2 adds a new row with a surrogate key and valid-from and valid-to dates, preserving history, such as a customer moving region. In Power BI, Type 2 needs facts to carry the surrogate key, and the warehouse should handle it rather than Power Query.
23
Totals look inflated after adding a second fact table. What could be wrong?
Likely a relationship is joining at the wrong grain or creating a many-to-many path, so rows get duplicated in filtering, or a bi-directional relationship is letting one fact filter another unexpectedly. I would check the relationships, keys and cardinality, make filters flow from shared dimensions only, and verify a measure against a known total in the source system using a simple table visual.
24
How do you use a bridge table for a many-to-many scenario?
For example, accounts can have multiple customers and customers multiple accounts. Create a bridge table with unique Account and Customer pairs. Relate Customer to Bridge one-to-many, and Account to Bridge one-to-many, with the Bridge to Account relationship set to filter in both directions or use CROSSFILTER in measures. Facts relate to Account. Test totals carefully because shared accounts count once per customer.
Surrogate keys are system-generated integer keys, such as ProductKey, used in dimensions instead of business keys like product codes. They handle changes in business keys, support Type 2 history with multiple rows per business key, and integer keys compress and join better in VertiPaq than long text keys. They are usually created in the data warehouse layer.
Frequently asked questions
How important is Data Modelling in Power BI interviews?
A good model makes DAX simple and reports fast. This topic covers star schema, fact and dimension tables, grain, relationship cardinality, cross-filter direction, active and inactive relationships, role-playing dimensions, date tables, fiscal calendars, bridge tables for many-to-many, slowly changing dimensions and surrogate keys, all common modelling interview questions.
How should I use the Data Modelling sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.