Power BI Service - Analyze in Excel - One to one relationship

[GPr0]

I've created a very simple dataset in Power BI Service with two tables, joined through a one-to-one relationship.

Table 1:

Table 2:

Model:

In Power BI service, whenever I create a report based on this dataset, data is displayed as expected : one row in Table 1 matches with one and only one row in Table 2 :

However, whenever I use the "Analyze In Excel" feature, the result is quite unexpected, and it looks like a cartesian product of Table 1 and Table 2  :

Is this behaviour expected when using the "Anlayze in Excel" feature ?
Thanks in advance for your help.

[Pragati11]

HI @GPr0 ,

ANALYZE IN EXCEL option in a way imports your Power BI model to an excel and helps you to analyze the data further.

In you case you have created ONE-to-ONE relationship between your tables, but it is BI-DIRECTIONAL.

BI_DIRECTIONAL refers here both tables can filter eachother.

But BI-DIRECTIONAL relationship introduces extra joins with the rows of your tables and creates every single possible combination, like you see at your end.

Check the following link on Analyze in Excel in Power BI for details:

https://exceleratorbi.com.au/power-bi-analyze-excel-what-you-need-to-know/

Thanks,

Pragati

[GPr0]

Thanks for your reply.

What I don't really get is : why is the behaviour different from a Power BI Report, where one-to-one relationships don't generate cartesian products ?

[Pragati11]

Hi @GPr0 ,

It actually does it in the background because your relation is bi-directional.

If you move exactly all the columns from TABLE1 against all the columns in TABLE2 in a table visual in Power BI, you will end up with similar join behaviour as you see in Excel.

Thanks,

Pragati

[GPr0]

It actually doesn't : there is no cartesian product in Power BI when using a Table Visual for example (see screenshot in my first post). 

[GPr0 · Accepted solution]

I found the answer here :
https://community.powerbi.com/t5/Power-Query/Relationships-in-PBI-Publisher-for-Excel/td-p/226105

Without measures, the relationships are not taken into account.

Thanks for your help.