Module 2 — Data Modeling

A strong Power BI model begins with two decisions: what each table represents and what one row in each fact table means. These decisions define the grain of the model and determine whether later measures can answer the required questions correctly.

A fact table normally contains measurable business events or snapshots—for example, one row per sales transaction, one row per support ticket, or one row per daily inventory snapshot. A dimension table contains descriptive attributes used to filter and group those facts, such as Product, Customer, Date, Store or Employee.

Power BI models work best when these tables are organized into a star schema. Dimension tables sit around a central fact table and connect to it through keys. The dimension side normally contains one unique row per entity, while the fact table can contain many rows for the same dimension key.

Why grain matters

• If the fact table is already aggregated to one row per month and product, you cannot later count individual transactions because that detail no longer exists.
• If you need distinct customers, transaction counts or analysis by transaction attributes, the fact table must retain the required level of detail.
• Different business processes often deserve separate fact tables rather than being forced into one table with inconsistent grain.
• Keys used to relate facts to dimensions should identify the intended dimension row consistently. Surrogate keys are common in warehouse designs because they separate model relationships from changing business identifiers.

Many-to-many business relationships

Some entities genuinely relate many-to-many—for example, one consultant can work on many projects and one project can have many consultants. A bridge table can store the valid consultant-project combinations and connect both dimensions through controlled one-to-many relationships. This is usually clearer than relying on an ambiguous direct many-to-many relationship.

Relationships allow filters to move between tables. To reason about a Power BI model, always ask three questions: what is the cardinality, which direction can the filter travel, and is the relationship active?

In a normal star schema, a dimension has a unique key and is on the one side of a one-to-many relationship. The fact table contains repeated foreign keys and is on the many side. This relationship lets a filter such as Product Category = Bikes restrict the fact rows that contribute to a measure.

Cross-filter direction

With single-direction filtering, filters normally travel from the dimension to the fact table. This direction is predictable, efficient and is the default best practice for most star schemas.

Bidirectional filtering allows filters to travel in both directions. It can solve specific modeling requirements, but it also makes the model harder to reason about and can create multiple possible filter paths. When two paths can carry the same filter between tables, Power BI can encounter ambiguity.

Cardinality terminology

• One-to-many (1:*) is the standard dimension-to-fact pattern.
• One-to-one (1:1) requires unique values on both sides and is less common in analytical models.
• Many-to-many (*:*) allows duplicates on both sides but should be used deliberately because filter behavior can be harder to understand.
• Column cardinality means the number of distinct values stored in a column. It is a storage/model-optimization concept and is different from relationship cardinality.

Before creating a one-to-many relationship, make sure the key on the one side is truly unique. If it is not, correct the dimension design or choose a modeling pattern that represents the real relationship.

Time intelligence and date filtering are much more reliable when a model contains a dedicated Date dimension rather than relying only on date columns scattered through fact tables.

A good Date table contains one row for every date in the required range and usually adds attributes such as Year, Quarter, Month Name, Month Number and Year-Month. The date column used as the primary date key should be unique and nonblank. For classic time-intelligence scenarios, the range should be continuous so functions can reason about adjacent periods correctly.

Sorting date labels

Text labels sort alphabetically unless you give Power BI a numeric or sequential sort column. For example, Month Name should be sorted by Month Number so January appears before February. A Month-Year label such as “Jan 2026” should use a YearMonth key such as 202601; sorting only by Month Number would mix months from different years.

Multiple date roles

A fact table often contains more than one date, such as Order Date, Ship Date and Invoice Date. Power BI can store several relationships between the same Date table and fact table, but only one can be active at a time. The active relationship is used automatically for normal filtering.

USERELATIONSHIP() tells CALCULATE() to use an existing inactive relationship for a particular calculation. This is useful when one Date slicer should usually filter Order Date but a specific measure must calculate by Ship Date.

Power BI offers several ways to create derived logic, and the correct choice depends on whether the result is stored or calculated at query time, whether it is row-level or aggregate, and whether users need to group or filter by it.

Measures are usually the preferred choice for analytical calculations because the same measure can return different results for different report filters. A measure such as [Total Sales] can show one value for the whole report, another by product, and another by month without storing separate results.

Calculated columns are useful when every row needs a stable category that users must place in a slicer, row header or axis. Because the values are stored, high-cardinality calculated columns can increase model size. If a static transformation can be performed in the source or Power Query, doing it before load is often more efficient and keeps preparation logic together.

Calculated tables can be useful for model structures created from existing model data, but source-system or Power Query transformations are generally preferable when they can produce the same stable table earlier in the pipeline.

Quick measures

Quick measures use a guided interface to generate DAX for common calculations. They can save time, but for PL-300 you should still understand the generated DAX. The exam can test the underlying calculation behavior even if the feature created the formula for you.

Power BI performance depends not only on DAX but also on how much data the model stores and how efficiently VertiPaq can encode it. Good model optimization starts by removing data the report does not need and by avoiding unnecessary detail.

VertiPaq is a columnar storage engine. It compresses repeated values very efficiently, so columns with relatively few distinct values usually compress well. High-cardinality columns—especially long text, unique transaction identifiers and precise timestamps—can consume much more memory because they contain many distinct values.

Practical model optimization

• Remove unused columns before load. A hidden column still occupies model memory; hiding only changes the authoring experience.
• Remove rows that are outside the analytical requirement when it is safe to do so.
• Reduce unnecessary granularity. If the report only analyzes dates, storing second-level timestamps may add cardinality without analytical value.
• Use appropriate data types and precision. Smaller, simpler representations can improve compression.
• Set meaningful names, formatting and data categories so report authors use fields correctly.
• For numeric identifiers such as Product ID, use Don't summarize when summing the number has no business meaning.

Diagnosing performance

Performance Analyzer in Power BI Desktop records how long visuals take to execute, including DAX query time and rendering-related activity. Use it when you need to identify which report visuals are slow before deciding how to optimize them.

DAX query view allows you to write and run DAX queries against the model. It is useful for inspecting model results and investigating calculations. A slow report can come from inefficient measures, poor relationship design, too many visuals, source latency in DirectQuery, or an oversized model—not just from one DAX formula.