Managing relationships between datasets with date variable

[KC011]

Hello,

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!

[lbendlin · Accepted solution]

clean up your data and create a data model

Adding injuries to that chart will create cognitive overload.  Use separate visuals.

[v-csrikanth]

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.

[KC011]

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!

[KC011]

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.

[lbendlin · Accepted solution]

clean up your data and create a data model

Adding injuries to that chart will create cognitive overload.  Use separate visuals.

[KC011]

Hello,

I have managed to create the data model and happy with results. I have a follow up question for the same dataset and would appreciate your guidance.

If I want to assign specific data (e.g. Start Number, Origin, etc) to each pen as per Dimension Table included in the responses below, how do I go about doing that? I have created a table for each type of data (e.g. Origin) but I am not sure how I should create the relationship with the rest of the data (Injury data in particular). I do not think simply including the Start Number/Origin data in the Injury dataset of the relevant pen solves this, assuming it will generate plenty of null data.

[lbendlin · Accepted solution]

Read about how to handle SCD2 - Slowly Changing Dimension Type 2

[KC011]

Hello, thank you for the response and solution. That is greatly appreciated. I will work on the solutions that you provided in these days.

[v-csrikanth]

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.

[KC011]

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

[lbendlin]

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.

[KC011]

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.

[KC011]

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!

[KC011]

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?

[lbendlin]

No, the recommended approach is to use a calendar dimension table in the data model.

[lbendlin]

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?

[lbendlin]

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).