What is a star schema, and why does Power BI work best with one?
A star schema puts one fact table (events such as sales lines, with numbers and keys) in the middle and dimension tables (customers, products, dates) around it, each joined one to many to the fact. Filters flow from the dimensions to the fact, the engine compresses narrow tables well, and DAX stays simple. A single wide flat table repeats every attribute on every row and makes some calculations awkward. See Microsoft's star schema guidance.
What is the difference between a fact table and a dimension table?
A fact table records events or measurements: one row per sale, visit or transaction, with numeric columns to aggregate and keys to the dimensions. It is long and narrow. A dimension table describes the things in those events: one row per product, customer or date, with the text columns you slice by. It is short and wide. Facts answer "how much", dimensions answer "by what".
What relationship cardinalities does Power BI support?
One to many (and its mirror, many to one), one to one, and many to many. In a one to many relationship the column on the one side must hold unique values; a duplicate key there is an error when you create the relationship or refresh. One to one is rare and usually means the two tables can be merged. A many to many relationship joins two tables where neither side is unique, typically facts at different grains.
What is cross filter direction, and when would you set it to Both?
Single means the one side filters the many side: a Product filter reaches Sales, but Sales does not filter Product. Both lets filters flow either way, which a bridge table in a many to many design needs. Set model wide, Both can make filter paths ambiguous, slow queries down and give results nobody expected. Prefer Single, and turn on the other direction for one measure with
CROSSFILTERwhen you need it.What is an inactive relationship, and how do you use one?
Only one relationship between two tables can be active. If Sales has an order date and a ship date, both can point at the date table, but one of them is inactive (shown dashed). A measure turns it on for its own calculation with
USERELATIONSHIP:Sales by Ship Date = CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[Ship Date], 'Date'[Date] ) )The other design is a second date table (a role playing dimension) when readers want to slice by both dates on the same page.
How do you model a many to many relationship, such as customers and joint bank accounts?
Add a bridge table with one row per customer and account pair. Customer filters Bridge one to many, Account filters Bridge one to many, and Bridge filters Account in both directions (on that relationship only, or with
CROSSFILTERinside the measures). Now a customer filter reaches the accounts they share. Totals are then not additive: a joint account counts once for each owner and once in the grand total, which is correct but must be explained to readers.Why do you need a date table, and what makes one valid?
Time intelligence functions need a table with one row per date and no gaps, covering whole years of your data, with a unique column of type date, marked as a date table. It also holds the attributes people slice by: year, quarter, month name, weekday, fiscal periods. Build it in Power Query or with DAX:
Date = ADDCOLUMNS ( CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2025, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month Number", MONTH ( [Date] ), "Month", FORMAT ( [Date], "mmm" ) )Relate it to every date column of the facts.
Why do many developers turn off Auto date/time?
With Auto date/time on, Power BI creates a hidden date table for every date column in the model. On a large model that is dozens of hidden tables, more memory and a slower refresh, and each hierarchy only filters its own column, so one date slicer cannot drive two fact tables. One shared date table, marked as such, replaces all of them. The setting is under Options, Data Load, for the current file and globally.
What is the grain of a fact table, and what goes wrong when two facts have different grains?
The grain is what one row means: one sales line per order, one budget value per category per month. Sales at product and day and a budget at category and month cannot sit in one table. Keep them as two fact tables that share the dimensions at the coarser level (a Category table, and the month in the date table), and make sure the budget measures show nothing below their grain, for example at product level.
Star schema or snowflake: which is better in Power BI?
A star, in most models. A snowflake splits a dimension into chains (Product to Subcategory to Category), which adds relationships, longer filter paths and more tables in the Fields pane, and saves almost no memory because the engine already compresses repeated text well. Flatten the chain into one Product table in Power Query. Keep a separate table only when it is shared, for example a Category table that both Sales and Budget relate to.
What is an ambiguous relationship path, and how does Power BI handle it?
A path is ambiguous when a filter could reach a table by two routes, for example when relationships set to Both create a loop. Power BI refuses to make a relationship active if it would create a second active path, and leaves it inactive. The fix is a cleaner model: filter directions set to Single, role playing tables where one table plays two parts, and
USERELATIONSHIPorCROSSFILTERin the few measures that need another path.
Power BI Data Modeling Interview Questions
Star schemas, relationships, cardinality, filter direction, date tables and the modeling mistakes that break totals.