cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

@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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors