Articles
Power BI Composite Model Interview Questions: Import, DirectQuery, and Cross-Source Relationships
Prepare for Power BI composite model interviews with Import, DirectQuery, Dual, limited relationships, unmatched targets, freshness, and query-path checks.
A report combines live sales with an imported target spreadsheet. Its visible yearly target rows add to 200, but the grand total says 230. Changing the relationship from many-to-many to one-to-many does not fix the discrepancy. Neither does changing every table to Dual.
Power BI composite model interview questions test whether you can explain where data is queried and how relationships behave across that boundary. A strong answer starts with table grain, storage mode, and source groups, then traces one visual. It avoids promising that a familiar relationship symbol creates familiar totals.
Evidence boundary: Product behavior comes from current Microsoft documentation. The miniature sales case and diagnostic exercises are original preparation material. They are not candidate reports, employer predictions, or an executed Power BI benchmark. This guide focuses on relational Import, DirectQuery, and Dual combinations; current Direct Lake composite configurations have additional rules.
What is a composite model, and what is merely multiple imported sources?
Microsoft’s composite model documentation distinguishes combining storage modes from simply importing several sources. Importing a SQL table and a spreadsheet does not by itself create a composite model. Combining imported data with DirectQuery data, or connecting multiple DirectQuery sources, raises different source-group considerations.
A useful interview inventory for our original case is:
| Table | Row grain | Storage mode | Origin |
|---|---|---|---|
| Sales | One sale | DirectQuery | Warehouse A |
| ReportingYear | One reporting year | DirectQuery initially | Warehouse A |
| AnnualTarget | One target per year | Import | Planning file |
Sales and ReportingYear can be queried within the warehouse source group. AnnualTarget lives in the imported-data cache. The relationship joining ReportingYear to AnnualTarget crosses that boundary, even if their year values appear simple and unique.
The origin of the data is not enough to determine the query boundary. Imported tables from different origins share the import source group. A DirectQuery connection to a remote Power BI semantic model is also different from a relational SQL connection: you are querying a model with its own measures and semantics.
Start by asking whether the report extends an existing semantic model, joins operational sources, or needs a fresher fact table alongside local planning data. Those requirements explain why a composite design might exist. “Composite is faster” does not: mixing sources introduces work as well as flexibility.
How do Import, DirectQuery, and Dual affect a query?
Microsoft’s storage-mode reference describes Import as a stored snapshot and DirectQuery as source access when visuals query data. Dual allows eligible tables to participate through either cached or DirectQuery behavior, depending on the query. It is a table property, not a synonym for an entire composite model.
For a warehouse dimension used with a DirectQuery fact table, Dual can support cached dimension-only queries and source-side queries with that fact table. Keep the same-source condition explicit. Dual does not turn an arbitrary imported spreadsheet into a live warehouse table, nor does it consolidate two unrelated databases into one source group.
| Visual request | Path to examine | Interview implication |
|---|---|---|
| Only an eligible Dual dimension | Cached dimension path | Fast slicer values can still be old |
| Dual dimension plus DirectQuery fact from its source | Native source query path | Inspect source execution and joins |
| Imported targets plus live warehouse measures | Cross-group evaluation | Inspect transferred filters and relationship semantics |
| Fields from two remote sources | Multiple source paths | Do not assume one source-side SQL join |
This is a query-review map, not a fixed count of source requests. Microsoft documents query-dependent behavior; caching, measures, visuals, and model shape can alter the actual work. Say that you will inspect the generated queries rather than promising every visual sends exactly one SQL statement.
Dual also does not mean a table contains historical Import partitions and a current DirectQuery partition. That describes a hybrid-table arrangement. Keep this article’s table-level storage discussion separate from incremental-refresh partition design.
Why can a one-to-many relationship still be limited?
Microsoft’s relationship reference identifies cross-source-group relationships as limited. A limited relationship does not become regular merely because you select one-to-many cardinality. Limited relationships do not provide the same table-expansion and blank-row behavior as regular relationships, and RELATED cannot retrieve a one-side value across them.
There are therefore three separate questions to answer: what cardinality you declared, which direction filters propagate, and how the engine evaluates the relationship. A diagram showing “1” and “*” answers only the first.
For our case, confirm that ReportingYear has a unique year key and that AnnualTarget has the stated grain. Then identify their source groups. Even clean one-to-many keys do not remove the cross-group evaluation boundary. Conversely, duplicate keys in a supposed dimension introduce a grain problem that a storage-mode change cannot repair.
Do not turn on bidirectional filtering as a general remedy for unexpected results. First write the intended filter path: a year selection should filter sales and targets for that year. Additional active paths can change which facts are constrained, and a total may then change for a legitimate but unintended reason.
An effective interview answer points to the relationship’s classification in Model view and tests an unmatched key. That small test exposes semantics that a perfectly matched sample can conceal.
How can visible target rows add to 200 while the total is 230?
Use this original fixture. ReportingYear contains 2025 and 2026. Warehouse Sales has amounts 100 and 60 for those years. Imported AnnualTarget contains three rows:
| Target year | Target amount | Matching ReportingYear row? |
|---|---|---|
| 2025 | 120 | Yes |
| 2026 | 80 | Yes |
| 2027 | 30 | No |
Define plain measures such as SUM(Sales[Amount]) and SUM(AnnualTarget[Amount]). Group the table visual by ReportingYear’s year column. Under the documented cross-group limited-relationship behavior, the unmatched 2027 target does not produce the regular relationship’s synthetic blank member. Visible matched target rows total 120 + 80 = 200.
At the grand total, absent a year filter or another restriction, the target measure evaluates over all three target rows: 120 + 80 + 30 = 230. Microsoft’s composite-model guidance documents this class of unmatched-key result. Our changed years and amounts make a small preparation fixture; the arithmetic was checked locally, but Power BI execution was not run for this guide.
The total is a measure evaluated in total context, not necessarily an addition of displayed cells. Replacing it with a sum over visible years would answer a different business question: matched-year targets only. It would conceal the missing 2027 dimension member unless you deliberately expose that limitation.
Ask which output the business wants. If future targets belong in the report, extend the authoritative reporting-year dimension and ensure the relevant cached copies refresh. If they should be excluded, make that exclusion explicit and test it. If unmatched keys represent bad data, report the count and amount rather than silently dropping them.
A relationship repair should therefore preserve the intended meaning of the measure. Matching a visually pleasing total is not enough evidence that the model is correct.
Can a composite report show a single fresh business snapshot?
Do not assume it. Microsoft’s storage-mode documentation notes that a Dual table’s cached values can differ from its source values. Query execution does not automatically hide those differences.
Suppose a new product P9 reaches the warehouse at noon. Its sales are available through DirectQuery, but a cached product slicer last refreshed at 11:00. A source-side visual and the cached slicer can expose different available values. This is an original scenario illustrating the documented cache/source distinction, not a promise that every report displays the same symptom.
For the interview, define freshness by table and use case. “Sales must be current within five minutes” does not necessarily mean the planning file must refresh every five minutes. Conversely, live facts are less useful if essential dimensions omit the keys needed to group them correctly.
A practical freshness contract can identify the last successful target import, dimension refresh, and source ingestion position. If the report needs a reproducible close-of-business view, consider a coordinated reporting snapshot or explicit as-of boundary at the source. A collection of current-looking visuals is not proof of cross-source transactional consistency.
Also distinguish data freshness from visual freshness. DirectQuery is source access during query evaluation; it is not a universal push subscription that redraws every open report immediately after every write. Discuss the report’s refresh behavior and observed query activity instead of equating DirectQuery with automatic live streaming.
How do you diagnose a slow cross-source visual?
Microsoft’s DirectQuery guidance emphasizes source performance and model limitations. A composite model still has to respect the capabilities of its DirectQuery tables. Increasing concurrency can increase source load; it is not a substitute for fixing expensive work.
Use an original diagnostic sequence that changes one boundary at a time:
- Establish the visual’s exact fields, filters, measures, and expected result.
- Test a warehouse-only version and an import-only version separately.
- Add the cross-group relationship and compare query work and elapsed time.
- Inspect relationship-key cardinality, missing keys, and transferred filter values.
- Compare source execution, gateway/network time, and semantic-model evaluation.
- Recheck correctness after each optimization, including unmatched keys and totals.
A visual grouping by a high-cardinality text identifier can impose much more cross-source work than a monthly summary. But raising the relationship grain from day to month removes daily detail. State that loss as a product trade-off, rather than describing a faster query as an unqualified improvement.
Similarly, an aggregation table helps only when its grain and measure mappings satisfy the query. A detail visual may still reach the fact source. Present a workload-specific comparison: which visuals benefit, which miss the aggregation, and what refresh cost the new table introduces.
In an interview, a useful proposal might be to place the shared reporting dimension with the warehouse facts, keep local targets small, and minimize cross-group paths. Another might consolidate the target data upstream. Choose based on ownership, update cadence, permissions, and workload; no single arrangement is best for every report.
What should you test before recommending a storage change?
Build a tiny correctness fixture before a large performance test. Include one matched key, one target-only key, one fact-only key, and a key added after the dimension cache refresh. Record both detailed rows and totals. Check that the grouping field comes from the intended table.
Then test the representative workload: dimension-only slicers, single-source aggregates, cross-source comparisons, and high-cardinality drill-downs. Compare cold and warm observations, source query counts, refresh duration, and capacity use without turning one timing into a universal speed claim.
Keep the decision reviewable. State which requirement forces DirectQuery, which tables can tolerate imported snapshots, why an eligible dimension would use Dual, and what cross-source relationships remain. If a simpler Import model meets the required freshness and scale, explain why introducing another boundary would help the reader’s actual report.
Five related modeling and reporting questions
The current catalog does not contain five dedicated Power BI composite-model questions. These verified exercises cover the surrounding decisions. The Tableau question uses different product semantics; apply its grain-and-join reasoning without transferring Tableau behavior to Power BI.
| Practice question | Relevant rehearsal |
|---|---|
| Describe building and improving a dashboard | Connect freshness, validation, and model choices to stakeholder decisions. |
| Model Data for Analytics, Reporting, and Applications | State facts, dimensions, history, and serving requirements. |
| Design KPI dashboard tables: retention, weekly rollups, timezone | Keep aggregation grain and time boundaries explicit. |
| Design an End-to-End Customer Delivery Experience Dashboard | Decide what a cross-source dashboard must reconcile. |
| Choose Between JOIN, BLEND, and RELATIONSHIP in Tableau | Practice explaining grain and unmatched records across modeling choices. |
Rehearse the 200-versus-230 case first, then use the Data Analyst collection to practice connecting technical behavior with a reporting decision.