How Power BI relation work?

[Anonymous]

Hi,

It is different from my understandings on relational DB. Need helps on understanding the Power BI relationship

I have a small example with just two tables

Table A

Table B

Their relationship

I would like to count the remark by IP. Presented them with a matrix

Even I just select count() on the remark, the result seemed like a count() distinct. Also, the sum is not correct. What I expected was:

I got this with merge queries

[tvaishnav]

To give you a gist,

Relation in power BI sets the direction of filtering. This filtering will happen over the columns that are related. 

Btw, I see that you are using bidirectional relation. In general, it is not a good practice because it can cause ambiguity in the data model.

[Anonymous]

@tvaishnav 

Thanks,  I am fully aware the potential problem of bidirectional relation. I can get what I want with the Merage Query and don’t even need to activate the relation between A and B. I just want to learn more about relation inside Power BI.

[Greg_Deckler · Accepted solution]

Might be wrong, but pretty sure it's the same result as a relational database. What you are seeing is that for IP = 1 in Table A, this essentially matches 2 rows in Table B, A and B. So, IP 1 has associated values of A and B in Table 1. There are two rows in Table B that match A and B. Seems like what you are after is more or less based on the cartesian product of the two tables, not sure of your join type. Multiple ways to do this. The simplest way would be just to use a COUNTROWS. I mean, if you want to count each row in Table A as an "instance" then why complicate matters? Makes no sense.

I mean, you don't *really* have two Remarks for IP = 1, ID = A, you have one remark, M1. Now, if you had another A reponse that was something like M4 and you had an M1 for A as well, that might make some amount of sense there.

Anyway, just guessing because not sure of the business scenario behind this.

[Anonymous]

Thanks @Greg_Deckler 

You mentioned “for IP = 1 in Table A, this essentially matches 2 rows”, it matched two distinct id, but it matched 3 rows.

I tested in PostgreSQL with inner join

postgres=# select * from a;
id|ip
A|1
A|1
B|1
B|2
B|2
C|3
(6 rows)

postgres=# select ip, count(remark)
postgres-# from a inner join b on a.id = b.id
postgres-# group by ip;
ip|count
2|2
3|1
1|3
(3 rows)

[Greg_Deckler]

Relationships are not inner joins.

Table A, IP = 1 has two associated values A and B. This matches 2 rows, the A and B rows in Table B. This is where the 2 comes from.

Relationships are relationships, they are what they are. If you want more control over join types, use Power Query Merge query, this provides the option of choosing what type of join you want.

I think your point of confusion is addressed in the following blog article: https://nisalbi.blogspot.com/2018/04/demystify-joins-in-power-bi.html