Power BI: Data Modelling, Relationships and Joins

Introduction

When I first approached a health dataset in Power BI, I was more interested in the dashboard than the structure behind it. I wanted to create visuals that could answer practical questions such as: How many patients visited each facility? Which health services were most used? How did patient visits change over time? Which locations recorded the highest activity?
I quickly realised that creating a chart was only part of the job. For a visual to answer a question accurately, the data underneath it must also be organised correctly. If tables are poorly structured or relationships are incorrect, a dashboard can look professional while still producing misleading results. This made data modelling an important part of my Power BI project.

Data Modelling

Data modelling involves organising tables and defining how they connect so that Power BI can correctly interpret and analyse the information. A good model supports accurate calculations, simpler DAX, efficient filtering, better performance and easier maintenance. I therefore began looking at my health dataset not just as rows and columns, but as information representing real-world events and entities. A patient visit, for example, is an event, while a patient, health facility, service or date provides descriptive information about that event.
This article follows my process of turning that health data into a model that can support reliable reporting. I explore fact and dimension tables, grain, flat, star and snowflake schemas, keys, relationships, cardinality, filter direction and referential integrity. I also examine Power Query Merge Queries and the six major join types, while explaining why a join is not the same thing as a Power BI relationship.
The goal is ultimately simple, to build a data model that allows the dashboard visuals to answer health-related questions accurately and flexibly.

Starting with Data Modelling

Before creating relationships or choosing a schema, I needed to understand what I was actually modelling. For this project, I used a simplified healthcare dataset built around patient visits. The information could be organised into tables such as:

• FactVisits – records individual patient visits and numerical information such as visit count or service cost.
• DimPatient – contains patient-related descriptive information.
• DimFacility – contains health facility information.
• DimService – describes the health services provided.
• DimDate – contains dates, months, quarters and years.
• DimLocation – contains geographical information such as county and sub-county.

This immediately gave me an important modelling question: What should each table represent, and how should the tables communicate with one another? A poor model could result in duplicated information, confusing relationships, inaccurate totals and complicated DAX calculations. It could also make the report harder to expand when new facilities, services or dates are added.
A good model, on the other hand, gives Power BI a clear structure. It also makes visualisation more flexible. For example, I should be able to select a facility from a slicer and have the relevant patient visits and services update automatically.

A structured data model serves as the foundation for comprehensive analysis, driving success in crucial areas such as:

1. Accurate Reporting – Ensures that visuals and figures reflect the underlying data correctly, reducing errors and misleading results.
2. DAX Calculations – Well-connected tables make it easier to create reliable measures and calculations without unnecessarily complicated formulas.
3. Performance – A properly structured model helps Power BI process queries efficiently, allowing dashboards and reports to respond faster.
4. Scalability – A good model can accommodate additional patients, facilities, services, and transactions as the health dataset grows without requiring a complete redesign.
5. Maintainability – Clearly organised tables and relationships make the model easier to understand, troubleshoot, update, and manage over time.

From a Flat Table to a Data Model

I started with the idea of storing all the health information in one flat table, where each row contained details about a patient visit, including the patient, facility, service, date, location and cost. Although this approach is simple, information such as facility names and service descriptions would be repeated every time a patient visited.
To make the data easier to analyse and maintain, I moved from a flat table to a structured data model. Instead of keeping everything in one table, the information is separated into a FactVisits table containing the actual visit records and several dimension tables containing descriptive information about patients, facilities, services, dates and locations.
These tables are then connected using keys and relationships. This approach reduces unnecessary duplication and provides a clearer structure for Power BI to filter data, perform DAX calculations and create accurate, flexible visualisations.

Fact Tables, Dimension Tables and Grain

Once I moved away from the flat table, the next question was how to organise the information. In this health dataset, I separated the data into a central FactVisits table and several dimension tables.
The FactVisits table records the actual events taking place. Each row represents a patient visit and contains measurable information such as the number of visits and cost, together with keys that connect the record to the relevant patient, facility, service and date.
An important concept here is grain, which defines exactly what one row in the fact table represents. For this model, the grain is one patient visit for a particular service, facility and date. Defining the grain clearly helps prevent double-counting and ensures that calculations and visualisations represent the health activity accurately.
The resulting structure can therefore be viewed as:
DimPatient → FactVisits ← DimFacility
DimService → FactVisits ← DimDate
DimLocation → FactVisits
This creates the foundation of a star schema, where the fact table sits at the centre and the dimension tables provide descriptive context around it.

Relationships in Power BI

After separating the health data into fact and dimension tables, the next step was to connect them. In Power BI, a relationship defines how two tables are linked using a common key. This is important because the information needed for analysis is often distributed across different tables rather than stored in one place.
In my model, FactVisits contains the actual patient visit records, while the dimension tables provide additional context. Connecting these tables allows Power BI to identify which patient each visit belongs to without repeatedly storing the patient's name and other details in the fact table.
The same approach is used for facilities, services, dates and locations. The dimension tables contain unique primary keys, while the fact table contains the corresponding foreign keys, which may appear multiple times because the same patient, facility or service can be associated with many visits.
These relationships allow filters and calculations to move between the tables. For example, selecting a particular facility can filter the visits recorded for that facility, allowing a visual to show the number of visits or total cost associated with it.
For this health dataset, the relationships form a star schema, with FactVisits at the centre and the dimension tables surrounding it. This structure keeps the model organised and makes it easier to create accurate and flexible reports.

Cardinality

With the relationships established, I then considered cardinality, which describes how many records in one table can relate to records in another table. Understanding cardinality is important because it determines how Power BI interprets the connections between tables.
The most common types are one-to-many (1:*), one-to-one (1:1) and many-to-many (:).
In my health data model, the main relationship is one-to-many (1:*). For example, one patient appears once in DimPatient, but that patient can have many records in FactVisits. Similarly, one facility can have many patient visits, and one service can be recorded across many visits. This makes the 1:* relationship appropriate for the star schema.
A one-to-one (1:1) relationship means that each record in one table corresponds to only one record in another table. This can occur when information about the same entity has been separated into two tables, although combining the information may sometimes be simpler.
A many-to-many (:) relationship occurs when multiple records on both sides can correspond to multiple records on the other side. For example, if patients could be associated with multiple health programmes and each programme could include multiple patients, a many-to-many relationship would exist. In Power BI, these relationships need to be handled carefully because they can create ambiguous filtering and unexpected results.
For this dataset, one-to-many relationships are the natural choice because the dimension tables contain unique identifiers while the fact table records repeated business events.

Active and Inactive Relationships

A relationship in Power BI can be either active or inactive. An active relationship is the default connection Power BI uses when filtering data and performing calculations. Inactive relationships are created when there is more than one possible relationship between the same tables, but only one can normally be active at a time.
A common example involves dates. A health organisation may want to analyse visits using both the visit date and a follow-up date. Both dates could connect to the same DimDate table. Power BI can keep one relationship active and the other inactive to avoid ambiguity.
In my model, the current DimDate to FactVisits relationship is active because DateID represents the date of the patient visit. If another date were added, such as a follow-up date, that second relationship could be inactive and activated only when specifically required in a DAX calculation.
This approach allows the same date dimension to support different types of date analysis without creating unnecessary tables or ambiguous filter paths.

Referential Integrity

Referential integrity means that the keys used to connect related tables should correctly correspond. In this health model, every foreign key in FactVisits should have a matching primary key in its related dimension table.
For example, if FactVisits contains FacilityID = F001, then F001 must exist in DimFacility. If a visit contains a facility ID that does not exist in the dimension table, Power BI may not be able to associate that visit with the correct facility. This can lead to blank categories, incorrect filtering or unexpected results in reports.
Maintaining referential integrity therefore improves the reliability of the data model. Before creating relationships, I would check that the IDs in the fact table have corresponding values in the dimension tables. This is particularly important in health reporting, where inaccurate or unmatched records could affect the interpretation of service utilisation and facility performance.

Filter Direction

After creating the relationships, I also needed to consider filter direction, which determines how filters move between related tables. In Power BI, filters can generally flow in one direction or in both directions.
For this health model, I used Single filter direction. This means a filter flows from the dimension table to the fact table. For example, if I select Nanyuki County Hospital from DimFacility, Power BI filters FactVisits to show only the visits associated with that facility.
This approach works well with a star schema because the dimension tables provide descriptive context while the fact table contains the events being analysed. It also keeps the model easier to understand and reduces the risk of creating ambiguous filter paths.
A Both or bidirectional filter allows filters to travel in both directions between tables. Although this can be useful in specific situations, it should be used carefully. In a larger model, bidirectional filtering can create multiple paths through which a filter can travel, potentially producing ambiguity and unexpected results.

A Join Is Not a Relationship

Although both a join and a relationship connect tables, they serve different purposes and happen at different stages of working with data.
A join is used in Power Query to combine data from two tables based on a matching column. For example, I could merge FactVisits with DimFacility using FacilityID to bring the facility name into the visit data. The result is a physically combined table containing columns from both sources.

A relationship, on the other hand, is created in the Power BI data model. It does not physically combine the tables. Instead, it tells Power BI how the tables are connected so that filters, calculations and visualisations can work across them.
This distinction is important because merging everything into one table can create unnecessary duplication and make the model harder to maintain. Keeping fact and dimension tables separate and connecting them through relationships is often more appropriate for a star schema.

1. Left Outer Join

It keeps all records from the left table and adds matching records from the right table. If there is no match, the right-side information is returned as blank.
Using the health facility example, Facilities is the left table and FacilityInspections is the right table. The result keeps all four facilities, while inspection information is added where a matching FacilityID exists.
Therefore, F001 and F002 receive their inspection status, while F003 and F004 remain in the result with blank inspection information. This type of join is useful when the records in the left table must be retained regardless of whether matching information exists in another table.

2. Right Outer Join

It keeps all records from the right table and adds matching records from the left table. This join is useful when the right-hand table represents the records that must all be preserved.
The Left and Right Anti joins are particularly useful for data-quality checks because they can identify records that exist in one dataset but are missing from another.

3. Inner Join

It keeps all records from the right table and adds matching records from the left table. This is useful when the analysis requires only records that exist in both datasets.

4. Full Outer Join

It keeps every record from both tables. Matching records are combined, while unmatched records from either table are retained with blank values on the opposite side.
In this example, the result would contain F001, F002, F003, F004 and F005. This provides a complete view of facilities appearing in either dataset.

5. Left Anti Join

This returns records that exist in the left table but have no matching record in the right table.
Using the facility data, F003 and F004 would be returned because they exist in Facilities but not in FacilityInspections.
This type of join is particularly useful for data-quality checks, such as identifying facilities that have not yet appeared in an inspection dataset.

6. Right Anti Join

It returns records that exist in the right table but have no matching record in the left table.
In this example, F005 would be returned because it appears in FacilityInspections but does not exist in Facilities.
This can help identify records that need investigation before being incorporated into a reporting model.

Where Merge Queries Fits into the Data Model

Merge Queries belongs to the Power Query stage, before the data reaches the final Power BI model. It is useful when columns from one table need to be brought into another table as part of data preparation.
Not every table needs to be merged. In my health dataset, keeping FactVisits and the dimension tables separate allows the model to retain a clear star-schema structure. A relationship can then connect the tables without physically duplicating their columns.
I would use Merge Queries when data needs to be transformed or combined, but use relationships when separate tables need to work together during analysis. Excessive merging could create wider tables, duplicate information and make the model more difficult to maintain.

The Model That Fits the Data Set

After comparing the different modelling approaches and applying relationships and joins to the health dataset, I selected a star schema as the final model. The reason is that the dataset is centred around patient visits, making FactVisits a natural fact table surrounded by descriptive dimensions.
The model contains FactVisits at the centre, connected directly to DimPatient, DimFacility, DimService, DimDate and DimLocation. The fact table stores measurable events such as visit count and cost, while the dimensions provide the context needed to analyse those events.
The star structure also makes the model easier to understand. Each dimension has a unique key, while the corresponding key can appear multiple times in FactVisits, creating one to many relationships. Filters flow from the dimensions towards the fact table using single-direction filtering.
This structure supports DAX calculations, report creation and filter propagation without requiring all the information to be physically combined into one table. It also reduces unnecessary duplication and makes the model easier to maintain if additional facilities, services or visits are added later.

A flat table would be simpler initially but would contain repeated descriptive information. A snowflake schema could further separate dimensions into additional related tables, but that would introduce more relationships and complexity than this relatively straightforward dataset requires. The star schema therefore provides a clear balance between simplicity, performance, scalability and maintainability for this analysis.

Dashboard Visualisation

I could now use Power BI visuals to answer practical questions from the health data. The purpose of a dashboard is not simply to display charts, but to present information in a way that makes patterns and comparisons easy to understand.
For this dataset, the visuals can answer questions such as: How many visits were recorded? Which facilities had the most visits? Which services were most used? How did visits change over time?

The data model makes these visuals interactive. For example, selecting a facility or service can automatically filter the relevant visit records through the established relationships. This demonstrates how good data modelling supports accurate and flexible dashboard visualisation.

Lessons Learned

This project taught me that effective data analysis starts with a well-structured model, not the visual itself. I learned how identifying the grain of a fact table, separating facts from dimensions, and creating the right relationships can improve the accuracy and clarity of a report.
I also gained a practical understanding of the difference between Power Query joins and Power BI relationships. Testing the different join types helped me see how data can be combined during preparation, while relationships allow separate tables to work together during analysis.
Coming from a public-health background, I found it useful to connect technical concepts to real health information. I could see how a well-designed model can turn records about patients, facilities, services and dates into information that is easier to interpret and use.
My biggest takeaway is that a good dashboard begins with good data modelling. When the foundation is accurate and organised, analysis becomes clearer, more flexible and more reliable.

Conclusion

This project has given me more confidence in using Power BI and has made data analytics feel more practical and achievable. I am beginning to see how I can combine what I already know about health with the technical skills I am developing. This experience has encouraged me to keep learning and to explore how I can use data to solve real problems and create meaningful insights in my future work.