Trouble on table relationships

[Sandritto]

Hi everyone,

I'm trying to build my Order Schedule in a Power BI report, but I'm having problems understanding or trying to figure out the correct relationships between tables.

First of all, the report has two different purposes. Show Orders and Production quantities along time and to save data week by week so you can check and compare Production quantities over weeks, e.g. Production orders are not fixed, so you might want to check on week 48 what you had planned for week 50, and then on week 49... Searching for variations.

Clarification: Data is saved running a batch with task scheduler, and appending data into different .txt files. One for each table.

On this image you'll understand it better: 

I am not getting the right data due to table relationships. As I am saving data week by week and there are several products, relationships are many to many, and its difficult to understand...

Here are my tables and the relationships:

Dates: Dates table

OWOR: Production orders

ORDR: Clients orders

OITW: Current stock

All tables have information from when they were taken.

As you can see OWOR and ORDR are related to DATES by the DueDate of the document, so that the matrix shows data correctly.

Trouble comes within the WeekSnapshots, i want to get data from past weeks but mixes up and doesn't show the right information.

Clarification: OITW (stock) and ORDR (orders) should be related by itemcode,but it wont let me due to many to many relationships with OWOR.

If you need any more information just let me know.

Any advice?

[Icey]

Hi @Sandritto ,

Please try to change the directions of all the relationships among your tables to Both.

For filtering purposes, both tables are treated as if they're a single table. For details, you can refer to this document.

And please tell me what is the result you want like?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sandritto]

Hi @Icey ,

The perfect result would be, to select a WeekSnapshot in the slicer and get the correct quantities/values (stock, client orders, production orders) for that concrete WeekSnapshot.

Regards

[Sandritto · Accepted solution]

Hi @Icey ,

I finally found a solution by creating new reference tables, deleted unwanted columns and removing duplicates.

Then use those reference tables as bridge tables between my tables. Create relationships and voilà.

Regards

[Icey]

Glad to hear that.😀

[Icey]

Hi @Sandritto ,

If you don't mind, please share me your PBIX file without real data and sensitive information.

Best Regards,

Icey

[Sandritto]

Hi @Icey,

Here is the PBIX file. 

You'll notice the field DateSnapshot is not changed, as i just copy-pasted invented data. The important thing is WeekSnapshot, which is changed.

If you need anything else, let me know.

Regards