Forum Discussion
How to create relationship between two tables
Hi,
This table I will call Problem
| Model Name | Problem | Value |
| A | Size | 10 |
| B | Size | 5 |
| C | Color | 3 |
| E | No Issue | 2 |
| E | Color | 1 |
This table I will call Order.
| Model Name | Value | Qty |
| A | 1 | 100 |
| A | 2 | 30 |
| C | 3 | 1 |
| D | 10 | 3 |
| C | 4 | 2 |
Create a reference to the Problem table and remove all but the model column.
Create a reference to the Order table and remove all but the model column.
Append the two new referenced tables together.
Remove the duplicates in Model column.
Join the new dimension table table to the two fact tables Order and Product.
You can now slice by model.
A video explaining the process: https://www.youtube.com/watch?v=2iEapc7yE_g
Cheers
John
Hi Thanks for helping, is it possible to slice by problem?
Actually I have lots of model, I would like to create a slicer that slice problem category and show the model/qty/value
Hi,
Sorry, I read your requirement incorrectly.
The original poster is partly correct, join both tables on Model. I assume you will have a many to many relationship. This will create a many to many realtionship but that will be ok. Make sure cross filter direction is set to both.
Then create a reference to the Problems table.
Remove all columns except the problem column.
Remove duplicates.Join the new Problem lookup (slicer / dimension) table to the other problem table on Problem. This will be a one-many relation. It will now slice both.
See images
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos CHi thanks for showing the step, however, i found the result in value is come out incorrectly. I put model/ problem and value in a table but the value is not matching the raw data.
You need to relate both tables using the model column.
That will create a many to many relationship.
In the relationship, make sure the cross filter direction between orders and problems is set to BOTH.
Then create a reference to the problem table, remove all column except but the problem column, then make a relationship between problem in the slicer to problem in the Problems table.
Here is the video: https://www.youtube.com/watch?v=Vq9kXU6am4Q
Here is the pbix: URL https://tmpfiles.org/dl/932942/jointwotables.pbix
Filename jointwotables.pbix
Size 26.82 KB
Expires at 2023-02-13 10:59 UTC
Filename jointwotables.pbix
Size 26.82 KB
URL https://tmpfiles.org/dl/932942/jointwotables.pbix
Expires at 2023-02-13 10:59 UTC