Building a data model in Power BI can be extremely easy. When primary keys match foreign keys one to one and when tables are built with a star schema in mind, things go smoothly. Other times, your relationships are much harder to build. Maybe the fields you’re trying to relate have formatting differences, or maybe you have multiple fact tables with different granularities of data. In this blog post we will examine an example of how to handle fact tables of differing granularities and the steps to take in Power Query to resolve thisfact of fact table issue.Â
What is a Fact of Fact Table?
A fact of fact table refers to a fact table that is related to the information given to you in your primary fact table, and it might also contain data that is at a different granularity than your primary fact table. It can also gives you information about another step in your data process relevant to the overall picture that your data is representing.
Dealing with multiple fact tables can be tricky, but hopefully, this example will give you an idea of how to deal with this issue going forward. I should mention, there are so many variables that will ultimately decide the route you choose, but this is just one use case that I found to be helpful in explaining this fact of fact concept.Â
Use Case Scenario
In the use case below we have data from a financial planning company. This financial planning company often holds events for current and prospective clients to generate appointments with new clients that could turn into new business. This client wants to know how many people that attend an event also attend a financial appointment within 30, 60, or 90 days of the event.Â
There are two fact tables in our data model below. Event Person Fact details all of the people that went to events and the Event Dim gives you details about each Event. Appointment Fact tells you about all of the appointments held by the company’s financial planning professionals.Â
The issue in relating these two tables is lots of people go to events but never go to an appointment, and lots of people go to appointments that never went to an event. Because of this complicated relationship between the two data sets, we need to create a bridge table.Â
Use Case Solution
Because of the complex nature of this relationship, we want to create a bridge table that will allow us to see the relationship between the two tables. Since the objective of this analysis is to analyze the impact that the events have, we are going to use the Event Person Fact table as the primary table. We will need to get the secondary table to the granularity of the Event fact table.Â
The Event Person Fact table is at the Event Id – Person Id – Event Date grain. This means that we will need to get our Appointment Fact table to the same grain. In this instance, we will be bringing in data at the Event Id – Person Id – Appointment Date grain to compare Date fields.Â
The Event Person Fact table is at the Event Id – Person Id – Event Date grain. This means that we will need to get our Appointment Fact table to the same grain. In this instance, we will be bringing in data at the Event Id – Person Id – Appointment Date grain to compare Date fields.Â
Join Tables – because we want to see what appointments resulted from events, LEFT JOIN the Appointment Fact table onto the Event Person Fact. Join the tables on the Person ID. This resulting data set will have each Person Id – Event Id repeating for every appointment that a person that went to an event, attended.
Calculate Date Logic – the new data format will allow you to compare every appointment date for a person that attended an appointment to an event date. Create new columns that compare whether the Appointment Date is within 30 days of the Event Date. The data isn’t granular enough for us to know whether one specific event lead to a specific appointment, but in this case we don’t need to know.
Group Rows – now that we’ve evaluated the appointment dates against the event dates, we want to take our new table back to the granularity of the Event Person Fact table. When we Group Rows at the Person Id – Event Id level we are left with the following table. I chose to SUM the values in the new columns to the right. You can then create more binary logic to analyze those values. Ex. IF(Appt Within 60 Days > 0, 1, 0)
Calculate Metrics – now that we have the number of appointments within certain time periods after events, we can calculate how effective events were at creating appointments. Count of the number of people and events ( in this case 4) and then how many people went to an appointment within 30 days of an event (50%), then within 60 days of an event (75%).
This is just one example use case of how to use Power Query to solve for when your table relationships are not pure.Â
Have more Power BI questions? Our team of Power BI experts are here to help!
