Problem with relationship between two tables and date table

[Philippe_Jamon]

Hi,

I'm fairly new to Power Bi and i have a problem on a report.

I searched in the forum but haven't found any exact solution for my problem.

I have a report with 4 tables.

Dechets_Tricotage is a table where I store scrap quantity for a given OF (Fabrication Order), where each row is a declaration of scrap.

Prod_Tricotage is a table where I store the production details linked to an OF (Efficiency of the machine, Qty produced...)

Date_Table is a table with all the dates between 2019 and 2025 and i use it for filtering

TimeTable is a table with all the hours and minutes of the day with the shift that correspond (AM or PM)

The result i want to obtain is a matrix with all the production data by half day (AM and PM) and also the scraps related.

I want to have dates in columns and machine OF and Reference in rows.

I made a model that almost works, but my problem is that when there is no production data for an half day (PM for example) but there are scraps detected during this half day, the scraps aren't showed in the Matrix.

Here you can see that on the 16 PM for the OF 37711 i have no scraps but in reality i had one.

I made an example PBIX file with similar data.

Link to PBIX file on One drive 

Link to PBIX file on Google drive 

Thanks for helping me.

[mangaus1111 · Accepted solution]

Hi @Philippe_Jamon ,

this should be your final data model

and here the correct result

[Philippe_Jamon]

Hi @mangaus1111 ,

I have One last question.

For the Machine dim table, it's ok because it won't change but for the OF dim table, i need it to be incremented by the Prod_tricotage fact table.

Is it possible ?

Thanks un advance 

[Philippe_Jamon]

Hi @mangaus1111 ,

thanks for your help, i think i understood your solution.

Br, 

Philippe 

[mangaus1111]

Hi @Philippe_Jamon ,

the problem in your model is that one fact table filter another fact table and this is why you don´t get the number of the scraps on 16th Nov PM.

Make a data model in the correct way like this one

Furthermore you need to create a third dimension table called 'Machine' or 'OF' that is going to filter in your report both the fact tables. If you use the column [Machine] from the 'Prod_Tricotage' table as filter in your report you get only the values of the 'Prod_Tricotage' table.

On the other hand, if you use the column [Machine] from the 'Deschets_Tricotage' table you get only the values of the 'Deschets_Tricotage' table.

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