A well-structured data model is the foundation of accurate, maintainable Power BI reports. Here are three key takeaways from this video:
- Many-to-many relationships should be a last resort. While they can technically connect tables that share a repeating key, many-to-many relationships often introduce ambiguity, unintended filter flow, and performance issues. One-to-many relationships are the preferred standard in most scenarios.
- Bi-directional filter flow requires careful control. When filters can travel in both directions between tables, unrelated dimensions like product category can inadvertently filter tables like marketing touches. Switching to single-direction filtering prevents these cross-contamination issues.
- A good data model reduces your dependence on complex DAX. When filter flow is set up correctly through relationships, your DAX measures become cleaner and more intuitive. DAX should build on top of a solid model rather than serve as a workaround for modeling problems.
This lesson is a preview from our Power BI Certification Course Online (includes software & exam). Enroll in this course for detailed lessons, live instructor support, and project-based training.
This is a lesson preview only. For the full lesson, purchase the course here.
One of the most common data modeling challenges in Power BI arises when the same entity, such as a customer, appears in multiple tables that serve different purposes. In this example, a sales report needs to track how much revenue was generated within a certain number of days after a marketing campaign touched a customer. The customer key appears in both the sales table and the marketing touches table, but these two tables are not directly related to each other.
The intuitive first instinct is to create a direct relationship between the customer key in sales and the customer key in marketing touches. Since a single customer can appear multiple times in both tables (multiple purchases, multiple campaign touches), this creates a many-to-many relationship. Power BI will allow this, but it comes with a warning for good reason.
Why Many-to-Many Relationships Cause Problems
Many-to-many relationships can lead to ambiguous filter behavior that produces misleading results. When a bi-directional many-to-many relationship connects sales and marketing touches, filters from one side can bleed into the other in unintended ways. For example, filtering by a product category like "Accessories" should only affect sales data, but with a bi-directional many-to-many relationship, that product filter can travel through the sales table and into marketing touches, incorrectly reducing the count of touched customers.
This happens because filter flow in a bi-directional relationship travels in both directions. A product category filter flows from the product dimension through the sales table and then crosses over into the marketing touches table. Suddenly, a filter that should only affect what products are displayed is also changing which marketing data appears in the report.
Controlling Filter Direction
The first step toward resolving this issue is controlling the direction of filter flow. By changing the cross-filter direction from bi-directional to single-direction, you can allow marketing touches to filter sales (which is the desired behavior for attributing revenue to campaigns) while preventing sales-side filters like product category from flowing back into the marketing touches table.
This adjustment immediately fixes the product category problem: touched customer counts remain accurate regardless of what product filters are applied. However, this approach may still have limitations depending on what other calculations and visuals need to work in the report.
An Alternative Approach: Filtering Through the Customer Table
A cleaner modeling approach involves routing the relationship through an intermediary table rather than creating a direct connection between sales and marketing touches. By making the relationship between customers and marketing touches bi-directional, marketing campaign selections can filter the customer table, which in turn filters the sales table through its existing relationship. This creates an indirect but controlled path from marketing touches through customers to sales.
This approach preserves the ability to filter in both directions where it is appropriate (between customers and their marketing touches) while avoiding the problematic direct many-to-many link between sales and marketing data.
Data Modeling Before DAX
One of the most important lessons from this exercise is the relationship between data modeling and DAX. When the data model has structural issues, it is tempting to write complex DAX measures to work around them. A DAX measure that explicitly counts customers from the marketing touches table may appear to produce correct results even when the underlying model is broken, effectively masking the problem.
While DAX is a powerful tool, it should build on top of a correct data model rather than compensate for a flawed one. When filter flow is configured properly through relationships, DAX measures become simpler, more transparent, and easier for other team members to understand and maintain. The goal is to get the data flowing correctly through the model first, and then use DAX to add the specific calculations and business logic that the model alone cannot express.
