Forum Discussion
Managing relationships between datasets with date variable
I am a new user of Power BI and am currently working on establishing relationships between several datasets that include variables:
- Feeding data for different groups of farm animals (recorded daily, with one record per date; includes zero values).
- Environment data (recorded hourly).
- Injury data for each pen of farm animals (not necessarily daily, with multiple records per date and no zero values).
I want to create relationships between these datasets and have followed advice in the post "Managing relationships between two data sources (06-06-2024 02:01 PM)," to create a date table, which I believe is needed for linking the datasets. I set up a relationship between feeding data for one of the animal groups in the dataset and one of the environmental parameters in the dataset. However, I am unsure if I have done this correctly, as I have not been able to visualize the data on the same graph (line and column chart), or have possibly been trying to set up the graph wrong.
Eventually, I would like to add more parameters specific to each group of animals to check whether the above variables are influenced by these new parameters.
Any guidance to help me troubleshoot this and set me up to continue creating this model would be appreciated!
Thank you!
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
16 Replies
You will want to combine "like" tables (including a source identifier column), otherwise your data model will quickly become unmanageable.
Think about the nature of your data and try to separate it into facts (something you can summarize) and dimensions (something you can filter by).
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.
Thank you.
Please see samples of datasets below that I would like to create relationships for. I have different records for different dates, and relating records across different datasets for corresponding dates for each pen, then between different pens, is what I am after. Not sure if this clarifies..
Feeding dataset for one of the pens:Date Feed weight Sun 07 Jul 0 Mon 08 Jul 0 Tue 09 Jul 0 Wed 10 Jul 480 Thu 11 Jul 960 Fri 12 Jul 960 Sat 13 Jul 1,200 Sun 14 Jul 0 Mon 15 Jul 0 Tue 16 Jul 800 Wed 17 Jul 2,000 Injured weight records for one of the pens:
Date Injured ind. weight 06-Jul 130 07-Jul 200 10-Jul 170 11-Jul 50 12-Jul 150 13-Jul 200 13-Jul 200 14-Jul 220 15-Jul 200 15-Jul 200 18-Jul 150 18-Jul 80 Temperature dataset:
30/06/2024 19:00 25.113 30/06/2024 20:00 25.21 30/06/2024 21:00 25.453 30/06/2024 22:00 25.283 30/06/2024 23:00 25.331 01/07/2024 00:00 24.968 01/07/2024 01:00 25.283 01/07/2024 02:00 24.895 Dimension table:
Pen Number Start Number Start Weight Start Average Origin A1 1000 10000 10 UK A2 999 12000 12 UK A3 1200 11000 9 US A4 1300 10000 8 US A5 1010 10000 10 ITA Your tables are not coherent enough. The date format is all over the palceand is missing the year, the Pen ID is missing from the tables, and the temperature dates have no overlap with the injury dates. The start date is missing for the Pen table.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Hello and apologies for the late response. I have amended the tables. Please see sample data for weight of feed supplied between July and August for two pens, A1 and A2, below.
Date A1 A2 2024-07-21 0 0 2024-07-22 2,400 800 2024-07-23 0 0 2024-07-24 3,200 2,400 2024-07-25 4,800 4,000 2024-07-26 6,400 4,000 2024-07-27 8,000 5,600 Below, the weight records for injured individuals from two sample pens A1 and A2 during the same period.
Date A1 Injured individual weight 2024-07-21 150 2024-07-21 150 2024-07-22 240 2024-07-23 25 2024-07-24 200 2024-07-24 200 2024-07-25 180 2024-07-25 150 2024-07-25 150 2024-07-26 120 2024-07-27 160 2024-07-27 160 2024-07-27 160 Date A2 Injured individual weight 2024-07-22 180 2024-07-22 50 2024-07-23 150 2024-07-23 150 2024-07-23 100 2024-07-24 50 2024-07-24 50 2024-07-24 190 2024-07-24 190 2024-07-24 190 2024-07-24 190 2024-07-24 190 2024-07-25 250 2024-07-25 250 2024-07-25 250 2024-07-25 250 2024-07-25 50 2024-07-27 250 Below is the temperature data for the same period.
Date Time Temp 2024-07-21 00:00:00 23.9938 2024-07-21 12:00:00 24.0136 2024-07-22 00:00:00 24.0335 2024-07-22 12:00:00 24.0534 2024-07-23 00:00:00 24.0733 2024-07-23 12:00:00 24.0931 2024-07-24 00:00:00 24.113 2024-07-24 12:00:00 24.1329 2024-07-25 00:00:00 24.1527 2024-07-25 12:00:00 24.1726 2024-07-26 00:00:00 24.1925 2024-07-26 12:00:00 24.2124 2024-07-27 00:00:00 24.871 2024-07-27 12:00:00 25.841 Cage Number Start Date Start Number Start Weight Start Average Origin A1 2024-07-21 1000 10000 10 UK A2 2024-07-22 999 12000 12 UK I want to have plots similar to the below to compare feeding and/or injuries across multiple pens, and to which I can add the temperature profile. Eventually I will want to include variables like 'Origin' to compare 'Injuries' between pens with different origin.
I hope this clarifies and I appreciate your help! Thank you.
Hi KC011
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions by lbendlin.
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!
Best Regards,
Community Support Team _ C Srikanth.Hi KC011
Please accept the response which address and meets your requirements so that the other community members will be helpful if they faces the similar issue.
Regards,
Community Support Team _ C Srikanth.Hello v-csrikanth, I have uploaded sample data in the requested format and will wait for any additional feedback that lbendlin can kindly provide to hopefully resolve the issue. Thank you.
clean up your data and create a data model
Adding injuries to that chart will create cognitive overload. Use separate visuals.
Hi lbendlin and v-csrikanth
I hope you're both doing well! I wanted to thank you again for the solution you provided earlier – everything is working perfectly now, and I’m happy with the results of the data model.
I have a follow-up question regarding the same dataset and was wondering if it’s possible to provide additional feedback on this or if I need to post a new question instead. I’m looking to assign certain data to each pen based on the Dimension Table. I’ve created separate tables for each type of data but I’m uncertain how to establish the relationship with the rest of the data.
Any guidance on how to approach this would be greatly appreciated.
Thanks again for all your help!
