Forum Discussion
Matrix table not showing all the values between two tables
Hi everyone,
I have been trying to create a table from two datasets Orders and Prev Orders.
Orders dataset looks like this:
| UniqueID | Order | Sign Date | Products | Amount | Prev Order | Prev Order Unique ID |
1 A | 1 | 01/11/2022 | A | 500 | 4 | 4 A |
| 1 B | 1 | 01/11/2022 | B | 100 | 4 | 4 B |
1 C | 1 | 01/11/2022 | C | 300 | 4 | 4 C |
2 A | 2 | 9/15/2021 | A | 500 | 5 | 5 A |
2 B | 2 | 9/15/2021 | B | 100 | 5 | 5 B |
3 D | 3 | 4/28/2022 | D | 700 |
Unique Id is just a concatenation of Order and Products.
Prev Orders Dataset:
| UniqueID | Order | Close Date | Products | Amount |
| 4 A | 4 | 01/11/2021 | A | 600 |
| 4 B | 4 | 01/11/2021 | B | 200 |
| 4 C | 4 | 01/11/2021 | C | 500 |
| 4 D | 4 | 01/11/2021 | D | 600 |
| 5 A | 5 | 9/15/2020 | A | 200 |
| 5 B | 5 | 9/15/2020 | B | 100 |
| 6 E | 6 | 4/28/2021 | E | 200 |
| 6 F | 6 | 4/28/2021 | F | 100 |
The relationship is based on column "UniqueID" from table 2 to "Prev Order Unique ID" from table 1.
Here's what I am currently getting when creating a table:
| Order | Table1.Products | Table1.Amount | Table2.Order | Table2.Products | Table2.Amount |
| 1 | A | $500 | 4 | A | $600 |
| 1 | B | $100 | 4 | B | $200 |
| 1 | C | $300 | 4 | C | $500 |
| 2 | A | $500 | 5 | A | $200 |
| 2 | B | $100 | 5 | B | $100 |
| 3 | D | $700 |
What I need to get:
| Order | Table1.Products | Table1.Amount | Table2.Order | Table2.Products | Table2.Amount |
| 1 | A | $500 | 4 | A | $600 |
| 1 | B | $100 | 4 | B | $200 |
| 1 | C | $300 | 4 | C | $500 |
| 1 | 4 | D | $600 | ||
| 2 | A | $500 | 5 | A | $200 |
| 2 | B | $100 | 5 | B | $100 |
| 3 | D | $700 | |||
| 6 | F | $100 | |||
| 6 | E | $200 |
Could anyone help me out with what I am doing wrong here or what can I do to achieve the desired result? Any help would be much appreciated.
Please let me know if I can provide any more information.
Thanks in advance
5 Replies
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Unique Id is just a concatenation of Order and Productswhat made you choose that? Generally you want to separate order header information from order detail information.
How is order 3 associated to order 6 ?
