PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchA Power BI visual is only as correct as the path its filters take through the semantic model. If a slicer fails to reach the table being summarized, or reaches a table it should not, the visual is wrong however clean the DAX looks. Most of these errors come from four decisions made early: keep summary values (facts) apart from the labels people filter and group by (dimensions), keep each fact table at one consistent grain, connect dimensions to facts with one-to-many relationships on keys that are genuinely unique on the dimension side, and filter in a single direction unless a specific requirement justifies more.
This guide assumes a star schema because it fits most analytical work, but treats it as a strong default rather than a fixed law. Relationships in Power BI are not the same thing as SQL joins. They define how filters travel, so the sections below start with filter paths and then cover the mechanics of the relationship settings.
As an Amazon Associate I earn from qualifying purchases.
Separate facts from dimensions before building anything
Microsoft’s guidance on star schemas, published on Microsoft Learn as “Understand star schema and the importance for Power BI”, rests on one distinction. “Dimension tables enable filtering and grouping.” Fact tables hold the values you summarize. The guidance recommends tables that represent normalized fact and dimension data, and advises against mixing both roles in one table. A table that carries a sales amount alongside a long list of descriptive attributes is harder to filter correctly, which is why the two roles are usually kept apart.
Fact tables hold one grain
The grain of a fact table is what a single row represents. A sales table might store one row per order line, while a budget table stores one row per product category per month. Both are valid, but they cannot share a table without double counting or misleading totals. Decide the grain first, record it in the table’s description, and keep every row at that level. If a second level of detail is needed, create a second fact table rather than appending rows of a different shape to the first.
#1 Best Overall
Dimension tables hold one row per member
A dimension has one row per member: one row per customer, product, calendar date or region. Its key column must be unique, because the relationship depends on it. Descriptive columns such as category names, region names or fiscal months belong here rather than in the fact table. A dedicated date dimension is the usual choice for time filtering, because it gives every fact row a consistent calendar to match against.
Power Query prepares the tables, and snowflakes are a choice
Power Query is where source data is reshaped into model-ready tables. Columns are removed or renamed, keys are cleaned, and text and dates are typed correctly before they reach the model. Microsoft notes a practical exception to strict normalization: a snowflake dimension, such as Product linked to Subcategory and then to Category, can sometimes be denormalized into one model table when that makes the model simpler. The trade-off is repeated descriptive text in exchange for fewer relationships and a cleaner field list. Flattening suits a small, stable hierarchy. Keeping the chain suits levels that change independently or are shared with other dimensions.
What a relationship does, and how it differs from a join
A relationship connects one column in one table to one column in another, and it creates a path along which filters propagate. In a typical star schema, filters flow from dimensions toward facts. When a visual is filtered, each table on that path is limited, and a fact row appears only if it satisfies every filter that reaches its table. A slicer on Region and a slicer on Year, for example, combine as conditions that must both be true for each sales row.
Free tools Windows power users keep installed
One-click scans. No signup required.
The word “join” in Power BI refers to two different operations. Power Query’s Merge Queries builds a combined table physically during preparation and refresh. A model relationship copies no columns; it changes which rows a measure can see at query time.
Rank #2
| Aspect | Power Query merge | Model relationship |
|---|---|---|
| What it produces | A new, combined table containing the merged columns | A filter path between two existing tables |
| When it takes effect | During data preparation and refresh | At query time, whenever a visual or measure is evaluated |
| Typical use | Bringing a lookup column from another source into a table | Letting dimension slicers filter fact measures across the model |
Merging one fact table into another to avoid building a relationship usually produces a table at the wrong grain.
Choosing cardinality from the shape of your keys
Cardinality describes how many rows on each side of a relationship can share a key value. It is a statement about your data, not a setting to guess at.
| Cardinality | Key uniqueness | Typical use | What goes wrong when it does not match the data |
|---|---|---|---|
| One-to-many (1:*) or many-to-one (*:1) | Unique on the one side; repeats allowed on the many side | Dimension to fact; the default for most reports | A refresh can fail if the one side contains duplicate values |
| One-to-one (1:1) | Unique on both sides | Splitting one entity across two tables when each key appears once on each side | The relationship assumes uniqueness on both sides, so duplicates on either side make it wrong |
| Many-to-many (*:*) | Duplicates allowed on both sides | Facts at different grains or bridge-style cases, after deliberate design | Constrained filtering and possibly omitted rows when integrity issues exist |
Check uniqueness in the data, not only in Desktop
Power BI Desktop infers cardinality when you create a relationship from loaded data. That inference reflects the rows present at the time, so it can describe a shape that holds today but not the one the model should enforce over time. Confirm uniqueness in the source or in Power Query, then set the cardinality deliberately. A quick check inside the model is a measure that counts dimension keys appearing more than once. A result of 0 means the candidate key is unique.
Duplicate one-side keys =
COUNTROWS(
FILTER(
SUMMARIZE(Product, Product[ProductKey], "RowsPerKey", COUNTROWS(Product)),
[RowsPerKey] > 1
)
)To set the relationship deliberately:
- Go to Home > Manage relationships.
- Select the relationship and choose Edit.
- Set Cardinality to match the key shape you verified.
- Set Cross filter direction as described in the next section.
- Keep Make this relationship active ticked for the path that should be the default, then select OK.
Make the keys match
- Data types must be identical on both sides. A key stored as a whole number on one side and as text on the other will not match, even when the displayed values look the same.
- Remove stray spaces. Use Transform > Format > Trim in Power Query before relating text keys.
- Datetime columns hide time. A column that displays as a date can carry a time portion, so matching it to a date-only dimension finds nothing. In Power Query, select the column and use Transform > Date > Date Only when date matching is what you intend.
- Keep codes with leading zeros as text on both sides. The product code “00123” and the number 123 are different keys.
Cross-filter direction: single by default
Cross-filter direction sets which way a filter can travel across a relationship. Single direction passes filters from the dimension to the fact. Bi-directional filtering lets filters also travel from the fact back to the dimension. Microsoft recommends single direction for the common case and bi-directional filtering only where it is needed. Bi-directional relationships can create ambiguous paths and can negatively affect performance, so enabling one to fix a single visual can change the results of others.
A case that justifies bi-directional filtering
A legitimate case is a dimension-only list that must show only members with activity in a fact table. An example is a customer list filtered by a product category, where the link has to pass through the sales table. Before changing the setting, check whether a measure using CALCULATE can answer the question without changing the model. If the relationship is still needed, write down which path must carry the filter and which visuals it will change. Then compare those visuals with the setting on and off, and check that dimension-level totals have not shifted unexpectedly.
Inactive relationships for a second date
Power BI allows only one active relationship between two tables at a time, so a second link between the same pair creates an ambiguous path. A sales table holding both OrderDateKey and ShipDateKey, each pointing at a Date table, is the classic case. Keep the order date relationship active, clear Make this relationship active on the ship date relationship in Manage relationships, and select that path inside the measures that need it.
Sales by Ship Date =
CALCULATE(
SUM(Sales[SalesAmount]),
USERELATIONSHIP(Sales[ShipDateKey], 'Date'[DateKey])
)Many-to-many: avoid it as the default
Why two fact tables should not be linked directly
Microsoft generally does not recommend relating two fact tables directly with many-to-many cardinality. Such a link can constrain how visuals filter or group, and integrity issues can cause rows to be omitted. Consider a sales table at order-line grain and an inventory table with one row per product, warehouse and month. A direct link on Product follows whatever keys match across both tables, so a visual that combines sales and inventory can multiply or drop values in ways neither table shows on its own.
Recommended Free Tools
Shared dimensions are the usual alternative
The typical fix is to add dimensions and relate each fact table to them one-to-many. In the sales and inventory example, Date and Product become shared dimensions. Both fact tables relate to them, so a slicer on Product or a range of months filters both facts through the same paths. A visual that compares sales and inventory by product then works through the same member values in each fact.
When a many-to-many relationship is appropriate
Many-to-many modelling can be the right tool when facts sit at different grains and no shared dimension gives the correct answer, or when the business relationship is itself many-to-many, such as customers who belong to several households. Before recommending one, document four things: the grain of each table, the bridge or dimension designs you considered, the filter path a slicer takes to each measure, and how totals will be checked against the source. For the household case, a bridge table with one row per unique customer and household pair is usually the cleaner design. Relate the bridge to each dimension one-to-many rather than relating the dimensions to each other directly.
Compare the design options
The table compares the three approaches on the criteria that matter in practice.
| Criterion | Shared dimensions (1:*) | Fact-to-fact (*:*) | Bi-directional filtering |
|---|---|---|---|
| Key uniqueness and integrity | Unique key on each dimension, checked before relating; duplicate one-side keys can make refresh fail | Duplicates allowed on both sides; integrity issues can cause rows to be omitted | Depends on the keys in each relationship; does not correct key problems |
| Filter behavior and ambiguity | One predictable path from each dimension to each fact | Filters between facts can be constrained, and visuals may not filter or group as expected | Filters travel both ways and can create ambiguous paths |
| Reporting flexibility | A slicer on a shared dimension filters every linked fact | Limited for cross-fact slicing | Enables some cross-filter needs; appropriate only where required |
| Storage mode and freshness | Works across the storage modes described below; freshness follows the source | Not stated for specific storage modes in Microsoft’s many-to-many guidance | For DirectQuery, Microsoft advises avoiding it unless it is necessary |
| Query performance under the real workload | The pattern Microsoft’s star-schema guidance is built around; measure with your own visuals | Not stated in general; validate against your workload | May negatively affect performance; measure before and after enabling it |
Storage modes and performance
Tables in a Power BI model can use Import, DirectQuery or Dual storage, and a composite model mixes them. The right choice depends on source capabilities, freshness requirements, the features the model needs and how users actually query the report. There is no universal performance result for any of them.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
| Mode | Where the data is held | Freshness | Typical role |
|---|---|---|---|
| Import | Copied into the model | Current as of the last refresh | Data that can be refreshed on a schedule the business accepts |
| DirectQuery | Stays in the source; visuals query the source | Reflects the source at query time | Data that must be current, where source performance is adequate |
| Dual | Can act as imported or as DirectQuery depending on the query | Depends on the path used | A table that must serve both Import and DirectQuery queries in a composite model |
Aggregation tables
Microsoft’s DirectQuery guidance describes aggregation tables that hold imported summaries. Visuals asking for higher-level totals can then be answered from the imported table rather than the source. A common design keeps detailed sales in DirectQuery and imports a table at month and category grain for summary pages. The benefit appears only when visuals actually request that grain, so check the query pattern before adding the complexity.
Best Value
Relationships in DirectQuery models
In DirectQuery models, relationship choices influence the native queries sent to the source. Microsoft advises avoiding bi-directional filtering unless it is necessary, and notes that expensive calculations can produce costly native queries. To measure a visual, open View > Performance analyzer in Power BI Desktop, select Start recording, refresh the visual, and read the time each query took. Repeat the check against the production source, because a Desktop session does not reproduce concurrent users.
Validation and troubleshooting
When a visual is empty or returns unexpected numbers, work through the checks below in order. Microsoft’s troubleshooting guidance covers relationships, cardinality, active status, cross-filter direction and related columns. Microsoft revises its Learn pages over time, so confirm setting names against your version of Desktop. This order starts with the data itself, because a missing row explains more symptoms than any relationship setting.
- Place the fields in a Table visual and inspect the rows and totals it returns instead of judging from a chart.
- Open Data view from the left navigation and confirm the tables loaded rows and the columns you expect.
- Open Model view and confirm a relationship line exists between the two tables you expect to connect.
- Open Home > Manage relationships, select the relationship, and confirm its cardinality matches the key uniqueness you verified.
- Confirm Make this relationship active is ticked for the path that should be the default. An inactive relationship is used only by DAX functions such as USERELATIONSHIP.
- Check Cross filter direction and trace the route from the slicer table, through each relationship, to the table being summarized. If the route breaks at any link, the measure cannot see the filter.
- Confirm the exact columns joined, not just the table names. Two columns with similar names can hold different keys.
- Investigate the usual causes: unmatched keys, type mismatches, duplicate values on the one side, hidden time portions in date columns, and ambiguous paths.
| Symptom | Likely cause | Where to look |
|---|---|---|
| A dimension slicer has no effect on a fact measure | A missing or inactive relationship, or a broken filter route | Steps 3 to 6 |
| A visual is empty even though both tables have rows | A type mismatch or stray spaces in key columns | Step 7 and the key checks above |
| A valid date returns nothing in a date-only filter | A hidden time portion in a datetime column | The Date Only transform in Power Query |
| A (Blank) row carries part of a measure | Fact keys with no matching dimension row | Step 8, unmatched keys |
| Refresh fails with a relationship error | Duplicate values on the one side | The uniqueness measure shown earlier |
| Unexpected results across two fact tables | A direct fact-to-fact link or an unintended bi-directional path | Steps 4 and 6, and the many-to-many section |
Further reading
For a deeper treatment of grain, keys and star schemas than a practical guide can cover, look for Analyzing Data with Power BI and Power Pivot for Excel by Alberto Ferrari and Marco Russo. Microsoft Press’s listing describes coverage of modeling, tables, relationships, keys, star schemas and granularity. The edition listed was published on 28 April 2017, so check the current edition and format before buying, and treat any interface details in it as dated. Searching for “Power BI data modeling book” will surface it.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
