Power BI Advanced · Module 2

Complex Modelling Patterns

Learn how to model many-to-many relationships, bridge tables, factless facts, disconnected parameters and other analytical scenarios that cannot be handled by a simple star schema alone.

Level Advanced

Suggested Time 120 Minutes

Delivery Theory and Lab

Primary Tool Power BI Desktop

Module overview

A conventional star schema should remain the default design for most Power BI semantic models. However, real organizational data does not always fit into a single set of one-to-many relationships.

Examples include:

A customer who owns several accounts
An account jointly owned by several customers
A salesperson assigned to several regions
A student enrolled in several courses
A project involving several employees
Sales transactions and targets stored at different grains
User-controlled parameters that should not directly filter data

These scenarios require deliberate modelling patterns. An incorrect many-to-many design can produce duplicated results, ambiguous filter paths, poor performance or totals that appear correct but are conceptually invalid.

Central principle A complex model should not be complicated merely because the source data is complicated. Use the simplest pattern that represents the business relationship accurately.

Learning outcomes

After completing this module, participants will be able to:

Identify genuine many-to-many business relationships
Distinguish many-to-many dimension and fact-table scenarios
Design and validate a bridge table
Explain the purpose of a factless fact table
Use shared dimensions to connect multiple facts
Handle facts stored at different levels of granularity
Design disconnected parameter tables
Apply SELECTEDVALUE and TREATAS appropriately
Recognize ambiguous filter paths and double counting
Select an appropriate modelling pattern for a scenario

1. Why complex patterns are necessary

A standard star schema assumes that every dimension member has a unique key and that one dimension row can relate to many fact rows.

For example:

Standard one-to-many pattern

DimProduct
One row per product

FactSales
Many sales per product

Measures
Sales and profit

This design becomes insufficient when a dimension member can belong to multiple members of another dimension or when two analytical tables contain repeated relationship keys.

Do not begin by changing the cardinality When Power BI reports that neither relationship column is unique, first determine why duplicate values exist. A direct many-to-many relationship is not automatically the correct solution.

2. Understand relationship cardinality

Cardinality	Meaning	Typical use
One-to-many	One table contains a unique key; the other contains repeated matching values	Dimension-to-fact relationship
One-to-one	Both relationship columns contain unique values	Special cases where two tables share the same row-level grain
Many-to-many	Both relationship columns can contain duplicate values	Selected complex scenarios requiring careful validation

Cardinality is a technical relationship property, but the correct configuration must follow the business meaning of the data.

Recommended starting point Retain clear one-to-many dimension-to-fact relationships wherever possible. Introduce bridge tables or limited many-to-many patterns only where the business relationship genuinely requires them.

3. The three common many-to-many scenarios

Dimension to dimension

Two business entities have multiple associations in both directions.

Example: customers and bank accounts.

Fact to fact

Two fact tables contain repeated values and must be analysed through shared dimensions.

Example: sales actuals and sales targets.

Higher-grain fact

One fact table stores information at a less detailed level than another fact table.

Example: daily sales and monthly category targets.

4. Dimension-to-dimension relationships

Business scenario: customers and accounts

A customer may own multiple accounts, while an account may have multiple joint owners.

For example:

Customer	Account
Customer 91	Account 1
Customer 92	Account 1
Customer 92	Account 2
Customer 93	Account 2

Neither customer nor account can be stored as a single foreign key inside the other dimension without losing valid associations.

The recommended solution is a bridge table containing one row for each customer-account association.

Customer-account bridge pattern

DimCustomer
Unique CustomerID

BridgeCustomerAccount
CustomerID + AccountID

DimAccount
Unique AccountID

Required tables

Table	Grain	Key requirement
DimCustomer	One row per customer	CustomerID must be unique
DimAccount	One row per account	AccountID must be unique
BridgeCustomerAccount	One row per customer-account association	The CustomerID and AccountID combination should be unique

5. Bridge-table design

A bridge table resolves a many-to-many relationship by explicitly recording valid associations between business entities.

Characteristics of a well-designed bridge

Its grain is one valid association between two entities
It contains the keys required to identify both entities
Duplicate key combinations are removed or justified
Its filter behaviour is deliberately configured
Unmatched and unknown keys are handled
Its business ownership and refresh process are known

Bridge-table validation questions

What exactly does one bridge row represent?
Can the same association appear more than once?
Can an association become active or inactive over time?
Does the relationship require effective start and end dates?
Should one member receive full or allocated credit?
Could filter propagation create an ambiguous path?

Double-counting risk If an account has two owners, summing the complete account balance by customer assigns the full balance to both owners. The customer-level total can therefore exceed the actual account total.

6. Allocation in bridge-table models

Some many-to-many scenarios require an allocation rule. For example, a sale may be credited to several salespeople.

Sale	Salesperson	Allocation
Sale 1001	Amira	60%
Sale 1001	Daniel	40%

The bridge can store an allocation factor to prevent the sale from being counted twice.

Allocated Sales = SUMX( BridgeSaleSalesperson, BridgeSaleSalesperson[Allocation Percentage] * RELATED(FactSales[Sales Amount]) )

Business rule required Do not invent equal allocation merely because it is easy to calculate. Allocation must follow an approved business rule.

7. Factless fact tables

A factless fact table records business events or associations without containing numeric measure columns. It normally contains dimension keys only.

Event tracking

Records that an event happened, even when the event has no numeric amount.

Example: one customer signed in to a website at a particular date and time.

Entity association

Records a valid relationship between members of two dimensions.

Example: one salesperson is assigned to one sales region.

Example: employee training attendance

A factless table could contain:

EmployeeKey
CourseKey
DateKey
AttendanceStatusKey

Although the table has no amount column, a measure can count its rows:

Attendance Count = COUNTROWS(FactTrainingAttendance)

It can answer questions such as:

How many employees attended each course?
Which departments have the lowest participation?
How many courses did each employee complete?
How did attendance change by month?

8. Fact-to-fact analysis

Fact tables should generally not be directly related to each other. They should be connected through dimensions that represent compatible business entities.

Example: actual sales and targets

Shared-dimension pattern

FactSales
Actual sales

Shared Dimensions
Date, Product, Region

FactTargets
Sales targets

This structure permits the same date, product and region filters to affect both actual sales and targets.

Recommended pattern Connect multiple fact tables through shared dimensions. Avoid creating direct relationships between repeated keys in fact tables.

9. Facts with different grains

A common advanced modelling problem occurs when facts are stored at different levels of detail.

Example grains

Fact table	Grain
FactSales	One product, customer, store and day transaction line
FactTargets	One product category, region and month

Sales can be analysed by individual products and dates. Targets cannot, because their source grain is category and month.

Valid target analysis

Month, quarter and year
Product category or higher
Region or organizational total

Potentially misleading target analysis

Target by individual product
Target by individual transaction date
Target by customer when targets contain no customer key

Never imply nonexistent detail A monthly target should not be presented as a daily target unless an explicit, approved allocation rule distributes it across days.

10. Direct many-to-many relationships

Power BI supports direct many-to-many relationship cardinality. However, it should be used only after the business scenario and filter behaviour have been carefully evaluated.

Possible advantages

Fewer tables in selected simple scenarios
Direct representation of repeated relationship values
Useful when neither table can act as a unique dimension

Potential risks

Unexpected or non-additive totals
Ambiguous filter propagation
Reduced model clarity
Difficult troubleshooting
Limited relationships in certain composite-model scenarios

Design preference When a distinct business entity exists, create a proper dimension or bridge table instead of relying automatically on a direct many-to-many relationship.

11. Disconnected tables

A disconnected table has no physical relationship to the rest of the semantic model. It is intentionally used to capture a user selection or provide values for a calculation.

Common uses

What-if analysis

Let users select a discount, growth rate, exchange rate or cost adjustment.

Measure selection

Let users switch a visual between revenue, profit, quantity and customer count.

Scenario selection

Let users compare actual, budget, forecast and alternative scenarios.

Example: discount parameter

Create a disconnected table containing possible discount values:

Discount Parameter = GENERATESERIES(0, 0.50, 0.05)

Read the selected value with:

Selected Discount = SELECTEDVALUE( 'Discount Parameter'[Value], 0 )

Apply it in a measure:

Sales After Discount = [Total Sales] * (1 - [Selected Discount])

Why no relationship is needed The parameter is not intended to filter sales rows. Its selected value is retrieved by a measure and applied mathematically.

12. Virtual relationships with TREATAS

The TREATAS function applies values from one table as filters to columns in another table. It can create relationship-like behaviour inside a measure without adding a physical relationship to the model.

Example: disconnected region selection

Sales for Selected Region = CALCULATE( [Total Sales], TREATAS( VALUES('Region Selector'[Region]), DimRegion[Region] ) )

Appropriate uses

Specialized calculations using disconnected selectors
Applying filters between tables without a physical relationship
Resolving selected virtual-relationship scenarios

When not to use it

To conceal a poorly designed semantic model
When a clear physical relationship should exist
When users need consistent filter propagation across all measures

Maintainability principle A physical relationship is normally easier to discover and reuse. Use a virtual relationship when calculation-specific behaviour is genuinely required.

13. Composite keys and multi-column relationships

Power BI relationships normally connect one column in one table to one column in another table. When uniqueness depends on several columns, a composite key may be required.

Example

A monthly target may be identified by:

Year
Month
Region
Product category

A combined key can be created during data preparation:

TargetKey = Year & "|" & FORMAT(MonthNumber, "00") & "|" & RegionCode & "|" & CategoryCode

Composite-key safeguards

Use the same data type and format in both tables
Include a delimiter to prevent accidental collisions
Handle null and blank values consistently
Validate uniqueness on the intended one side
Create the key upstream where practical

In DirectQuery scenarios, Microsoft also documents COMBINEVALUES for selected multi-column relationship requirements.

14. Filter-direction decisions

Complex patterns sometimes require filters to cross a bridge table. Nevertheless, bidirectional filtering should not be enabled throughout the model without a specific reason.

Decision questions

Which table should initiate the filter?
Which business entities should become visible after filtering?
Does another active path already connect the tables?
Will row-level security use the same path?
Could the path produce ambiguous propagation?
Can the requirement be handled inside a measure instead?

Ambiguity risk If two active paths can propagate the same filter between tables, Power BI may reject the relationship or produce behaviour that is difficult to explain and maintain.

15. Pattern-selection guide

Business scenario	Recommended starting pattern
One unique dimension member relates to many facts	Standard one-to-many relationship
Two dimensions have multiple associations	Bridge or factless fact table
Two fact tables must be compared	Shared conformed dimensions
Actual and target facts have different grains	Separate fact tables with compatible shared dimensions
User selects a numerical assumption	Disconnected what-if parameter
User switches between measures	Field parameter or disconnected selector
A filter is required for only one calculation	Virtual relationship with TREATAS
Uniqueness depends on several columns	Validated composite key

16. Worked modelling example

Scenario

A consulting company needs to analyse project revenue by employee, department and skill. Each employee can work on many projects, and each project can involve many employees.

Proposed tables

Table	Grain
DimEmployee	One row per employee
DimProject	One row per project
BridgeProjectEmployee	One employee assignment to one project
FactProjectRevenue	One project and accounting month
DimDate	One row per calendar date

Design issue

Project revenue exists at project level, not employee level. If the full revenue is displayed for every assigned employee, totals will be duplicated.

Possible business solutions

Show project revenue only at project level
Allocate revenue equally among assigned employees
Allocate revenue according to recorded working hours
Allocate revenue using an approved contribution percentage

Recommended analytical solution Store an approved allocation factor in the bridge. Confirm that the allocation factors for each project total 100% before calculating employee-attributed revenue.

17. Model-validation process

Confirm every table's grain.
Write one sentence explaining what each row represents.

Test key uniqueness.
Confirm that every intended dimension key is unique.

Test bridge combinations.
Identify duplicated or missing entity associations.

Trace filter paths.
Verify which tables are filtered from each dimension.

Compare totals.
Check model totals against authoritative source totals.

Test multiple selections.
Validate totals when users select several bridge members.

Test blank and unknown members.
Confirm that unmatched keys do not silently remove facts.

Document non-additive behaviour.
Explain measures that cannot be summed across certain dimensions.

18. Common modelling mistakes

Mistake	Likely consequence	Recommended correction
Using many-to-many because duplicate keys exist	Unclear or incorrect filtering	Investigate the grain and create a proper dimension
Connecting two fact tables directly	Ambiguous filtering and duplicated values	Connect through shared dimensions
Leaving duplicate bridge combinations	Repeated associations and inflated calculations	Validate the bridge's composite key
Ignoring allocation requirements	Full values attributed to several members	Apply an approved allocation rule
Using bidirectional filtering everywhere	Ambiguous paths and performance problems	Enable it only for justified paths
Relating a parameter to the fact table	Parameter values incorrectly remove fact rows	Keep the parameter disconnected
Using TREATAS for every relationship	Hidden logic and difficult maintenance	Prefer physical relationships for reusable filtering
Comparing facts below their supported grain	Misleading targets, budgets or forecasts	Restrict analysis or apply governed allocation

19. Hands-on laboratory

Lab: Model project assignments and revenue

Participants receive employee, project, assignment, timesheet and monthly project-revenue tables.

Task 1: Declare the grain

Define the grain of every supplied table.
Identify dimensions, facts and bridges.
Identify repeated and unique keys.

Task 2: Create the bridge

Create one row per project-employee association.
Remove unjustified duplicate combinations.
Validate unmatched employees and projects.

Task 3: Configure relationships

Create dimension-to-bridge relationships.
Configure the required filter path.
Avoid ambiguous active relationships.

Task 4: Address allocation

Calculate each employee's project hours.
Calculate the employee's share of project hours.
Allocate project revenue using that share.
Confirm that allocated revenue equals total project revenue.

Task 5: Add a disconnected parameter

Create a projected growth-rate parameter.
Use the parameter in a projected-revenue measure.
Confirm that the parameter does not filter fact rows.

Expected deliverable

A validated semantic model containing employee and project dimensions, a project-assignment bridge, revenue facts, timesheet facts, allocation measures and a disconnected growth parameter.

20. Knowledge check

Question 1: What does one row in a bridge table normally represent?

One valid association between members of two business entities.

Question 2: What is a factless fact table?

A fact table that records events or entity associations using dimension keys but contains no numeric measure columns.

Question 3: Why should two fact tables usually not be related directly?

Both tables normally contain repeated keys. A direct relationship can produce ambiguous filters and duplicated results. Shared dimensions provide a clearer analytical path.

Question 4: When should a disconnected table be used?

When a table supplies user-selected values for calculations but should not directly filter model rows.

Question 5: What is the primary risk when a single amount is associated with multiple dimension members?

The amount may be counted once for every association, producing an inflated total unless a valid allocation rule is applied.

Question 6: What does TREATAS provide?

It applies values from one table as filters to columns in another table, creating relationship-like behaviour within a calculation.

21. Complex-model checklist

Every table has a documented grain
Many-to-many relationships reflect real business rules
Bridge rows represent unique valid associations
Shared dimensions connect compatible fact tables
Different fact-table grains are clearly documented
Allocation rules are approved and validated
Disconnected tables are intentionally disconnected
Virtual relationships are used only where appropriate
Bidirectional relationships do not create ambiguity
Totals agree with authoritative source totals
Blank and unmatched keys have been tested
Complex behaviour is documented for report creators

Power BI Advanced - Complex Modelling Patterns