Forum Discussion
Power BI Table relationship
Hi Experts!
I am connecting two tables by many:1 relationship on a joined field named Customer SKU.
My many side table (Table 1):
| Customer SKU | Sales |
| Customer 1 + SKU A | 10 |
| Customer 1 + SKU C | 2 |
| Customer 2 + SKU A | 5 |
| Customer 2 + SKU B | 10 |
| Customer 2 + SKU C | 5 |
My one side table (Table 2):
| Customer SKU | Target |
| Customer 1 + SKU A | 10 |
| Customer 1 + SKU B | 5 |
| Customer 1 + SKU C | 5 |
| Customer 2 + SKU A | 5 |
| Customer 2 + SKU B | 10 |
| Customer 2 + SKU C | 5 |
I want to produce a matrix table displaying information from two tables as below:
| Customer SKU | Target | Sales |
| Customer 1 + SKU A | 10 | 10 |
| Customer 1 + SKU B | 5 | 0 |
| Customer 1 + SKU C | 5 | 2 |
| Customer 2 + SKU A | 5 | 5 |
| Customer 2 + SKU B | 10 | 10 |
| Customer 2 + SKU C | 5 | 5 |
However, as Customer 1 + SKU B value does not exist in my Table 1, Power BI produce only below table without the row of Customer 1 + SKU B
| Customer SKU | Target | Sales |
| Customer 1 + SKU A | 10 | 10 |
| Customer 1 + SKU C | 5 | 2 |
| Customer 2 + SKU A | 5 | 5 |
| Customer 2 + SKU B | 10 | 10 |
| Customer 2 + SKU C | 5 | 5 |
Is there any way to obtain the full table, i.e. do not let Power BI filter out Customer 1 + SKU B from the matrix table result?
Appreciate your kind help!
Ha
Anonymous
As per rules, you need to have all values used in the fact table available in your dimension table which is Table 1 for you. In this case, either you can add the missing values to Table1 prior to loading the table in the source level or if possible, use Power Query, or, If you want to see the values based on Table 2, you can use the "Customer SKU" column from Table 2 in your matrix.
3 Replies
Anonymous
As per rules, you need to have all values used in the fact table available in your dimension table which is Table 1 for you. In this case, either you can add the missing values to Table1 prior to loading the table in the source level or if possible, use Power Query, or, If you want to see the values based on Table 2, you can use the "Customer SKU" column from Table 2 in your matrix.Thank Fowmy. I tried to use columns from Table 2 in the matrix but it still doesnt work. Can you give me more information on how to add the missing values in Table 1 using Power Query? The above illustration is just simple example. i actually have 10,000+ records hence it is not easy to add missing values in