Working with two datasets

[cocoloco79]

Hi everyone,

I'm stuck working with two datasets, trying to calcuate values in the same table.

I crated two uion tables and and connect them wioth a one to many relationship.

I also created a Date table and calcuated a Weekstart Start date, which I connected with those two datasets.

Sold Kg are doubeling up even though there should only be one value for Week start date 07/04 for is_organic "False".

I'm not sure how I can work two datasets into one table. 

Any help is much appreciated.

[MattiaFratello]

Hi @cocoloco79, what are you using for your relationship? Date or WeekStartDate? And what do you have in your fact table?

Also, why the other relationships are inactive? The dotted ones

[cocoloco79]

Hi @MattiaFratello ,

Releationship is between WeekStartDate, the other tables are not part of what I'm tring to do.

bi_budget table holds budgeted kilos by week, crop and crop type.
bi_dashboard table holds all the sales data, which I'm trying to match by crop, crop type and week start date.

Any suggestion on how to aproach this? creating a combined table perhaps? I'm not sure

[MattiaFratello]

Why your relationship btw bi_dashboard and bi_date is many to many? Can you show me this relationship? Can you open it for me and make a screenshot?

[cocoloco79]

Hi @MattiaFratello 

here is the screenshot:

[v-sgandrathi]

Hi @cocoloco79,

Happy to see you here in the Microsoft Fabric Community!

The doubling of Sold Kg values is caused by the many-to-many relationship between your bi_dashboard and bi_date tables on Week Start Date. To fix this, ensure your bi_date[Week Start Date] column contains unique dates (no duplicates) and both fact tables have matching date formats for their Week Start Date fields. 

Then, delete the current many-to-many relationship and recreate it as a one-to-many relationship with bi_date on the “one” side and your fact tables on the “many” side. This setup will allow proper filtering without duplications.

If you still need to analyze budget and actual sales together, consider combining these tables into a single fact table with an indicator column. Please try adjusting the relationships this way and check if Sold Kg aggregates correctly. Let me know if you need help with this.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Thank you,

Sahasra.

[v-sgandrathi]

Hi @cocoloco79,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[cocoloco79]

Hi, I created a combined table, inculding the many to many relationship. 

BI_Date = CALENDAR(date(2024,01,01), date(2028,12,31))

Many to many relationship is causedn as the calendar is exeeding todays date, I believe.
Is there a function to increase the dates as the dataset grows?

Anyways, even with the many to many relationsp, the table works correctly now.

[v-sgandrathi]

Hi @cocoloco79,

Thank you for the additional details and clarification.

You're correct in identifying that the many-to-many relationship may be caused by the calendar table extending beyond the actual range of dates used in your dataset. When the calendar includes dates not present in related tables, it can sometimes result in unintended relationships or ambiguous joins.

To address your question-- yes, it is possible to make the date range dynamic so that it automatically grows or adjusts based on the data. This is commonly done by creating the date table using the actual minimum and maximum dates found across your source tables. Doing so ensures that the calendar only includes dates relevant to your data, which can help reduce the risk of many-to-many relationships and improve model performance.

In Power BI, this can be achieved using Power Query or DAX. If you prefer not to use DAX, Power Query offers the ability to reference the date range from your fact tables and generate a calendar accordingly during data load.

Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.

Thank you.

[v-sgandrathi]

Hi @cocoloco79,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you..

[v-sgandrathi · Accepted solution]

Hi @cocoloco79,

Hi ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and give us Kudos. Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @cocoloco79,

Accepting a helpful reply can assist other users in the community by addressing similar issues. Thank you for your support, and have a great day.

We encourage you to continue using the Microsoft Fabric Community Forum.