Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Relationships slow down very much the performance


I thought that the performance is so weak due to the measures applied. But I found out that the attributes from the dimensions table slow down the performance very much.

Here is my model.
Sales Cond = fact table with customer number (of payer, receiver of products, receiver of invoice), product number, country, turnover
Customer Master 1 = dimensional table that includes details of customer (payer)
Customer Master 2 = dimensional table that includes details of customer (receiver of products)

Customer Master 3 = dimensional table that includes details of customer (receiver of invoice)

The 3 dimensional tables are all the same. The relationsship differs depending on which customer number is chosen for the relationship (payer, etc.). Basically I use all 3 dimensional tables to get the customer name.




The VertiPaq Analyzer shows that each customer_number_key (to link the tables) takes 4% of the total DB size in the dimensional tables. Cardinality is about 5m. However, the customer key in fact table is only 0,1% of DB and cardinality is about 9.000. The high difference is because only a few fact records are loaded while the dimensional tables includes all records.


The output is a pivot where the filter is set on coutry and month/year. The rows include customer number, customer text (just one combination) and 15 measures. If I change the country, the model cannot be calculated. I get a timeout. If I takeout the customer text, I've no issues.

What is causing the problem? Do I need to add the customer name as a relatedcolumn in the fact table? But I thought as much as possible should be with the dimensional tables!?

Another question in that sense. The tables are linked by one directional filters. Unfortunately, if I put customer names as filter in the excel pivot, it shows all customers regardless which country is filtered. How can this be solved? Only by using both-directional relationsships which may decrease performance?


Thanks a lot in advance.

Frequent Visitor

Speed increased very much after using only one fact table. In the same term the size decreased dramatically as the key with high cardinality that links both fact tables diminished as well.

The joining is done directly in oracle DB source.

Super User
Super User

You don't have to load the same dimension table three times. You can load it once, add the relationships ( all but the first will be inactive) and then use the USERELATIONSHIP function to activate it per measure.  You should indeed also have the FactTable related to the customer table via customer.



I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors