Forum Discussion
Managing relationships between datasets with date variable
clean up your data and create a data model
Adding injuries to that chart will create cognitive overload. Use separate visuals.
Read about how to handle SCD2 - Slowly Changing Dimension Type 2
Hello,
Thank you for your quick feedback.
The different datasets share common pen numbers, which I believe could serve as a link between them. Unless I have misunderstood, this might be what makes them "like" tables, and if so, combining datasets based on pen number seems to make sense to me. Still, I am unsure how best to approach this in Power BI, given that the variables they represent are recorded on different dates. Since the rows do not match by date across datasets, I created a 'Date' table to help structure the model.
Regarding the source identifier, I assume you are referring to 'Date,' but please let me know if I have misunderstood. When you mention combining the tables, are you suggesting merging queries, or would it be better to keep the tables separate and establish relationships between them?
My datasets have fact tables for feeding and injury records and a dimension table for dates, but having some issues structuring the model to create manageable relationships before things get complicated. I am still new to Power BI so your guidance would be helpful!
The source identifier would be your original table name (unless you don't care which table a row came from)
Maybe you can provide some sample data?
Perhaps I could add rows to datasets that do not have daily records to ensure they include all dates, assigning zero values where necessary. This might simplify the process of combining datasets or creating relationships.
I assume this would need to be done for the injury dataset by adding rows for missing dates and summarizing the records—such as summing the count and weight per date—to ensure one value per date for both feeding and injury datasets. I suppose the same approach could be applied to the hourly environmental data as well.
I’m not entirely sure if this approach makes sense, or if it’s feasible in Power Bi. Would this be a recommended way to proceed?
No, the recommended approach is to use a calendar dimension table in the data model.