PowerBi Cross Filteration without Relationship

[Anonymous]

Hello everyone

I've been working on PowerBi on-premises, and everything works fine except for one single issue. Basically, I've come from Google Data Studio due to major support towards connections (i.e. Microsoft SQL Server Database).

As Google Data Studio, we don't create relationships, most of the work is done by Data Studio itself, and one of that things isn't working in PowerBi.

Scenario for Data Studio

I have several data tables (i.e. Sales Table, Recovery Table, Outstanding Table) and there are multiple common fields in them (i.e. Customer Name, Sales Person Name, etc), in Data Studio if all these common fields have the same name in all three tables, it can filter correct values when one record is selected in one table.

I want to achieve the same results in Power Bi, but what I have done is, that Power Bi works on the relational model and we need to create a relationship of fields between tables, and when we select a record in the table, it will only search the similar field from the second table, this way we will not get the desired results.

I hope that I have described my issue properly...

[amitchandak]

@Anonymous , I think you need to try role playing dimension

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

[Anonymous]

Dear, Thanks for your time

The method of Role-Playing Dimension has the same effect as the previous. Suppose, I have Sales Info table  and Purchase Return Table, both have same field of Customer & Sales Person, but in between is Sales Officer table with active relationship on Sales Person field in both Sales and Purchase Return table. On report, I have two table to show, one is sales and another is purchase, here say I want to select customer in Sales table and I want to see same customer records in Purchase Return table which in practical I'm not getting. Here if I select customer from Sales table, I will get all customer under selected customer's Sales Person because actual relationship is on Sales Person.

It is to let you know the PowerBi Desktop I'm using doesn't support Many to Many relationship "maybe".