Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
runnerpaul
Regular Visitor

Having two instances of the same table

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:

tables.PNG

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

5 REPLIES 5
v-sihou-msft
Employee
Employee

@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.

 

111.jpg

222.jpg

@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
Not applicable

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

 

Regards,

Anunay

ankitpatira
Community Champion
Community Champion

@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.

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/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.