Having two instances of the same table

[runnerpaul]

Is it possible to use the same table twice but in each case have it linked in a different way to the other tables?

I have the below tables:

As you can see there is an active relationship with the OutsideRooms tables and an inactive relationships with the OutsideRoomsSales and OutsideRoomsQuotes. I'd like an active version of this table to relationships to these tables also so am thinking if I added anoter instance of it I might be able to do what I want.

Is this something I can do or should I avoid this?

Thanks

Paul

[v-sihou-msft]

@runnerpaul

In this scenario, you have a table pattern with loops, then cross filtering can create an ambiguous set of relationships.

The filter can go through the OutsideRooms table or go directly to OutsideRoomsSales/Quotes table here. So the existing inactive relationships here are proper as designed.

There are several different ways you can deal with this situation, here are two options:

1. Delete or mark relationships as inactive to reduce ambiguity. Then you might be able to set a relationship cross filtering as Both.
2. Bring in a table twice (with a different name the second time) to eliminate loops. This makes the pattern of relationships like a star schema. With a star schema all of the relationships can be set to Both.

You can take a look at following two star schemas as reference.

[Jfrick100]

@runnerpaul, I believe wrote, "Bring in a table twice (with a different name the second time)...".

How do you do this in Power BI?

[Anonymous]

Asking the same question : can anyone mention how to create 2 instances of the same table?

Regards,

Anunay

[ankitpatira]

@runnerpaul On top of what @MattAllington said and great article by him, guessing that you would want active relationships between all four tables if you change Cross Filter Direction for two active relations in your model from Both to Single then you will be able to make all four relations active provided your data is correct.

[MattAllington]

The issue is your date table is directly filtering your 2 data tables and indirectly filtering them as well (via the outsiderooms).  Plus you have bidirectional filters from your 2 data tables to outsiderooms, compounding the filters tripping over themselves. I am not sure what you are attempting to do, but I guess there is probably a better way. The generally accepted approach is to use a star schema. I wrote about that here. http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/