A semantic model is a contract between physical data and business questions. Its tables and relationships determine which numbers are possible; its measures and metadata determine what those numbers mean. When the design is weak, reports become slow, filters behave unpredictably and different teams publish contradictory totals. The Microsoft DP-600 exam expects analytics engineers to move beyond dragging fields onto a canvas and make deliberate choices about grain, relationships, calculation semantics and enterprise reuse.
Consider an organization with a finance dashboard showing revenue by month, a regional operations report and an executive scorecard. All three draw from the same business, yet each team could implement a slightly different definition of recognized revenue. A shared semantic model can consolidate those definitions, while still permitting reports to display them differently. The benefit disappears if the model’s facts are poorly defined or if its access rules cannot support the users who consume it.
Start by writing down the grain
Grain is the meaning of one row in a fact table. An order-line fact might represent one purchased product on one order, while a daily inventory snapshot fact represents a product at a location on a date. Those tables cannot be joined or aggregated casually as if they described the same events. If an order has three lines, counting rows as orders overstates activity. If the inventory snapshot repeats every day, summing daily stock balances yields a figure that does not represent actual stock.
Define each fact’s keys, measures, event time and expected uniqueness before building relationships. Then identify the dimensions that provide the descriptive context: date, customer, product, store and perhaps employee. A star schema puts dimensions around the relevant facts so filters travel along deliberate paths. It is not merely an aesthetic preference. Separating observations from descriptors gives the engine a more predictable model and makes it easier for a report author to choose a meaningful field.
If a source export includes customer address, product description and order value repeated on every line, loading it unchanged creates duplicate descriptive values and encourages inconsistent filtering. Prepare reusable dimensions and stable keys upstream where appropriate. The semantic layer can reshape smaller datasets, but large or shared enterprise transformations often belong in the lakehouse or warehouse. This architectural division is central to the Microsoft data and Fabric pathway.
Use relationships to express business meaning
A typical dimension-to-fact relationship is one-to-many: each dimension key identifies a unique member, and many fact records reference it. Problems arise when modelers connect two tables because their column names happen to match without checking uniqueness or filter direction. If the supposed “one” side contains duplicates, the relationship may fail or force a many-to-many design that produces confusing results. Fix the underlying business key issue before reaching for an ambiguous relationship.
Bi-directional filtering is sometimes justified, but it should be a conscious choice with a known evaluation path. Allowing filters to propagate both directions across several relationships can create ambiguity, slower evaluation and totals that change unexpectedly when another visual adds a filter. Prefer a simple dimensional pattern where it expresses the requirement, and model exceptional scenarios explicitly rather than turning on every available filtering option.
Many-to-many relationships are not inherently incorrect; they often describe legitimate business structure. A salesperson can work in several territories, and a customer can belong to multiple programs. A bridge table can represent those memberships and make the intended path clearer. Ask whether the bridge applies at all dates or changes over time; a timeless relationship can misstate historical results when territory assignments were different last quarter.
Model time with intention
Time is especially easy to mishandle because a business can have many relevant dates. An order has a created date, paid date, shipped date and perhaps a returned date. One date dimension can support several role-playing relationships, but only one relationship between the same tables may be active in a typical design. Measures that need an alternate date relationship can use an explicit calculation strategy such as USERELATIONSHIP where appropriate. The author must tell users which event the measure counts.
Standard calendar periods and fiscal calendars may differ. A company that closes its accounting month on a non-calendar boundary should not build fiscal reporting by grouping the raw transaction timestamp alone. Create a date table with the intended fiscal mapping and verify year transitions, leap days and partial periods. A time-intelligence function cannot repair a badly defined business calendar; it merely executes a calculation over the dates supplied.
Slowly changing dimensions require equal care. If a customer changes region, should last year’s sales move to the new region or remain assigned to the region at the time? Both questions can be legitimate, but they are different metrics. The data design may need historical surrogate keys or effective-date logic. Make the choice before publishing a regional growth chart, or the trend can change retrospectively whenever a customer’s master record is updated.
Separate source columns from business measures
Calculated columns produce values at a table row level and can increase model size. Measures are evaluated in the context of a query and express aggregation and other dynamic logic. The distinction matters when a report slices data by date, region and product. A stored column containing an order’s value may be useful; a measure for revenue excluding returns should usually express the calculation under the current filter context. Attempting to precompute every possible filtered metric as a column quickly becomes impractical.
Make explicit measures for important business quantities and give them clear names, descriptions and number formats. “Margin” is ambiguous unless users know whether it means gross margin amount or margin percentage, and which cost components it includes. Hide technical keys and intermediate columns from report authors without making the underlying relationships incomprehensible to maintainers. A good semantic model communicates the vocabulary of the business, not the incidental naming of the ingestion system.
For a basic revenue measure, SUM(FactSales[NetAmount]) can be appropriate when the source fact already contains validated net amounts at the expected grain. If net revenue must subtract refunds stored in a separate fact, the measure must coordinate the two event structures and time definitions. No DAX expression compensates for silently counting a refund twice in an upstream table. Measure correctness therefore begins with a data contract and continues through the model.
Choose storage modes from constraints, not fashion
Import mode stores a refreshed copy of data in the model and often provides strong interactive performance, at the cost of refresh orchestration and memory. DirectQuery pushes supported query work toward the external source, making its response time and concurrency important. Direct Lake uses Delta-backed data with an in-memory column-loading approach, subject to its own capacity, freshness and fallback behavior. Composite models can combine modes when a use case genuinely requires them, but complexity should have a clear benefit.
Suppose a retail model has a massive event fact table and small slowly changing reference tables. The best arrangement depends on the update cadence, query patterns, data source and available capacity. A model that uses Direct Lake for eligible tables may perform very well, yet poorly optimized DAX or a hidden query fallback can negate the advantage. Another model may be better served by aggregating raw events into curated facts before they reach the semantic layer. Storage mode cannot be chosen independently from physical data design.
Operational requirements also influence the choice. How fresh must the report be? Can the business accept a scheduled snapshot? Who operates source credentials and security? What happens when a source is temporarily unavailable? A model that is fast during a demonstration but impossible to refresh predictably in production is not a successful design.
Make advanced modeling features earn their complexity
Calculation groups can reduce repeated measure logic for standardized transformations such as year-to-date, period comparisons or currency-format behavior. They are powerful, but they also make evaluation order and calculation precedence more important. Document assumptions and test interactions with existing measures. A calculation group used indiscriminately can transform metrics for which a time calculation or formatting rule is inappropriate.
Dynamic format strings preserve numeric measure behavior while changing its display according to context. They are preferable to converting a numeric measure into text solely to add a symbol or unit, because a text result cannot be aggregated or sorted as a number in the same way. Field parameters let report consumers choose displayed dimensions or measures, but the model must still prevent nonsensical combinations and maintain understandable labels.
Large semantic model formats and incremental refresh strategies can help at scale where supported, yet neither substitutes for good partitioning and reliable refresh dependencies. Test data ranges, model behavior after schema changes and the treatment of historical corrections. A partition policy optimized for append-only data may behave poorly when records are revised several months later. Align refresh design with the actual source update pattern.
Build reusable assets with clear ownership
A certified or otherwise endorsed shared model can reduce competing definitions across the enterprise. Give it an owner, documented dependencies, a change process and a clear permission model. A workspace that permits every report author to edit the central model is hard to govern. At the same time, a model that nobody can extend may encourage teams to create untracked copies. Decide which changes belong to the shared contract and which presentation choices remain local to reports.
Downstream impact analysis matters before changing or deleting a measure, column or relationship. A small model edit can break dozens of reports or silently change a KPI. Maintain a versioned definition, test representative visuals and consult owners of high-value downstream assets. The neighboring PL-300 Power BI skill set emphasizes report use and analysis; DP-600 adds the responsibility for governing the semantic foundation those reports depend on.
Validate with business questions, not only model diagrams
Construct a small set of known-answer tests: revenue for one invoice, order count for a multi-line basket, inventory at a point in time, historical sales by prior region and month-to-date totals across a fiscal boundary. Test filters across multiple dimensions and confirm the result matches independently calculated source evidence. A relationship can be syntactically valid while returning a misleading business result, so correctness requires more than a successful refresh.
Then examine performance under realistic concurrency and visual behavior. Hide irrelevant fields, reduce unnecessary high-cardinality columns, simplify filter paths and inspect expensive measures. Document the known limitations of the model and the meaning of major metrics. For DP-600, the durable skill is to defend every modeling choice: what the row means, how filters propagate, when a measure is evaluated, what security applies and how a change will affect existing consumers.