Forum Discussion
Relating two tables [Help]
Hello All, I need help trying to figure this one out: I have two tables that have the following columns Materials Num Type Supplier 1. A John ...
Here is your source data in slightly more usable format:
Materials:
| Num | Type | Supplier |
| 1 | A | John |
| 2 | B | John |
| 3 | C | John |
| 4 | A | Dan |
| 5 | B | Dan |
| 6 | C | Dan |
| 7 | A | Peter |
| 8 | B | Peter |
Management:
| ID | Date | Shift | Mat H1 | Mat H2 | Mat H3 | Kg Cons H1 | Kg Cons H2 | Kg Cons H3 |
| 1 | 12/1/2023 | 1 | 2 | 1 | 5 | 300 | 400 | 500 |
| 2 | 12/1/2023 | 2 | 3 | 1 | 4 | 320 | 280 | 600 |
| 3 | 13/1/2023 | 1 | 1 | 2 | 3 | 250 | 300 | 500 |
However, the management table is not usable in that format. A better format would be the one below. That way you can link both tables via the Material Number.
| ID | Date | Shift | Hopper | Material | Kg |
| 1 | 12/1/2023 | 1 | 1 | 2 | 300 |
| 2 | 12/1/2023 | 1 | 2 | 1 | 400 |
| 3 | 12/1/2023 | 1 | 3 | 5 | 500 |
| 4 | 12/1/2023 | 2 | 1 | 3 | 320 |
| 5 | 12/1/2023 | 2 | 2 | 1 | 280 |
| 6 | 12/1/2023 | 2 | 3 | 4 | 600 |
| 7 | 13/1/2023 | 1 | 1 | 1 | 250 |
| 8 | 13/1/2023 | 1 | 2 | 2 | 300 |
| 9 | 13/1/2023 | 1 | 3 | 3 | 500 |
Next step is to load this into the Power BI data model
Ideally you also have a calendar table in your data model
From there your report visuals nearly write themselves
see attached