How to create relationship between two tables

[SelflearningBi]

Model Name	Problem	Value
A	Size	10
B	Size	5
C	Color	3
E	No Issue	2
E	Color	1

Model Name	Value	Qty
A	1	100
A	2	30
C	3	1
D	10	3
C	4	2

I would like to create a slicer that filter by Problem . How to create relationship between these two tables?
ex: 
A Problem Slicer, visualize in a table or chart show value/qty in model level and different problems

Now im facting an issue that problem doesnt filter table 2 only filter table 1

[johnmelbourne]

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

[SelflearningBi]

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

[johnmelbourne]

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 C

[SelflearningBi]

Hi 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.

[johnmelbourne]

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

[LeoT]

@johnmelbourne Dude, you took the time to post 2 videos on YT with all the details on how to solve the problem. You are a legend!
Much respect. 👍👏

[johnmelbourne]

Thanks ! A comment 2 years after the work from a visitor is still valued.  Cheers. 😁

[pratyashasamal]

Hi @SelflearningBi  ,
I would suggest you to create a one to many relationship between Table 1 and Table 2 on basis of Model Name.
To Create relationship manually :-

1. On the Modeling tab, select Manage relationships > New.

2. In the Create relationship dialog box, in the first table drop-down list, select a table. Select the column you want to use in the relationship.

3. In the second table drop-down list, select the other table you want in the relationship. Select the other column you want to use, and then select OK.

Please follow this link to read more :-
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Hope this was helpful.
Thanks,
Pratyasha Samal
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 C

[SelflearningBi]

@pratyashasamal  hi thanks for heping, but doesnt solve my issue..