Relationships issues

[MrTD]

Setup: I have a unique identifier in my 'Person' table labelled ID. These identifiers exist in two other tables 'Person Attributes' and 'Person Events' but are not unique.  I have a relationship set from Person to these tables for the ID (one to many).

When I try to create a single visual using the ID and any fields from these two tables then the visual messes up with an error.

Any advice how I would create this visual/ fix this would be helpful. Much thanks.

[vcastello]

Hi @MrTDI think your problem is due to the bi-directional relationship.

If you look closer at your photo you'll see that there are two arrows in each line of the relationship. This indicates that the relationship is bi-directional.

Change it to 'One Way'. 

You can do it through ....

1.- Open the relationship tab.
2.- Select one relationship.
3.-Click on edit.
4.- On the bottom-right of the page you'll see something like (I'm translating from spanish) ... Direction of the cross filter. Click there.
5.- Choose One Way.
6.- Click on accept.
7.- Repeat for the other one.

That should work

Hope That Helps

Vicente

[MrTD]

@vcastello doesn't solve anything, thanks though!

[vcastello]

Hi @MrTD

Then, I'm sorry :- (

I don't know how I can help ...

Vicente

[SivaMani]

Hi @MrTD,

 You have unique values on a table and it's detailed in another 2 tables.

Let's take your sample data,

Table1 - ID(unique values)

Table2 - ID, Attribute

Table3 - ID, Travelled

Here is the issue,

Attribute and Travelled fields are having text values so we can't aggregate them.

Table2 is having 2 'a' values

Table3 is having 3 'a' values

obviously, the system can't detect the relationship between them. Suppose, any one of the tables has measures then it automatically aggregate it based on value 'a'

I hope I made it clear.

Let me know if you need further help.

Regards,

Siva

[MrTD]

Hey @SivaMani, thanks for the response.

I see. So Power BI doesn't detect the ID in the ID column even though I set up the relationship for it? A bit annoying hmm.
I've introduced a new column (all 1's) in my attributes table, and set the data type to whole number. By your comment shouldn't that mean that Power BI should aggregate these values and therefore the visual would be shown? 

I've tried this and the same problem still persists so this probably isn't the case. Thanks!

[SivaMani]

@MrTD,

Try Merge Queries. It will help you to achieve this 

[MrTD]

@SivaMani,  thanks once again!

This isn't optimal with a dataset that has millions of rows with various columns say. This surely would require a large amount of memory which again, isn't optimal! Thanks

[SivaMani]

Hi @MrTD,

It would be easy to help you if you share some sample data.

[MrTD]

Sure thing!
ID

a

b

c

d

e

f

ID|Attributes

a	young
a	tall
b	old
b	fat
b	grey
c	white
c	ears
d	hole
e	pie
f	rapture

ID|Travelled

a	amsterdam
a	brussels
a	paris
b	switzerland
c	austria
c	croatia
c	greece

Relationship should automatically be connected by ID edit: @SivaMani