locked
Relationship from one dimension table to two other dimension tables RRS feed

  • Question

  • I have a sales model containing the below tables. I am then asked to be able to see what each sales responsible has made of revenue. My issue here is that a sales manager can have multiple countries and also multiple profit centers. I therefore created a relationship from 'Sales responsible'[Profit Center] to 'Organization'[Profit Center] and also from 'Sales responsible'[Country code] to 'Country'[Country Code].

    As these are all dimension tables and my relationship is opposite of all other filter directions I get the result in the below Pivottable, which is obviosly wrong since I just get every sale repeated on every Sales responsible.

    How can I get the result I want?

    Thursday, October 20, 2016 1:48 PM

Answers

  • Morten_DK,

    I tried to edit my initial response, but messed it up.  In order to obtain the filter propagation from the 'Sales Responsible' table to the 'Sales Fact' table, you need to include the 'Sales Responsible' table in the filter context.

    Sum of Revenue:= CALCULATE(SUM('Sales Fact'[Revenue]),'Sales Responsible')

    • Marked as answer by Morten_DK Tuesday, October 25, 2016 8:44 AM
    Tuesday, October 25, 2016 8:25 AM

All replies



  • Friday, October 21, 2016 8:36 AM
  • Hi Morten_DK,

    In order to create a relationship in a data model in Power Pivot, at least one side of the relationship must be tied to a column that is unique in the table. You can create relationship in Diagram view.

    For your issue, you can refer to the follow steps.

    In the PowerPivot window, on the Home tab, in the View area, click Diagram View. You will see all the tables. To organize a comfortable view, use the Drag to Zoom control, select a column and drag to the another column that you want to prefer in different table. For example, please click 'Sales responsible'[Profit Center], and drag to 'Organization'[Profit Center], the one(click 'Sales responsible'[Profit Center]) to many('Organization'[Profit Center]) relationship is built, the order is important. The relationship is opposite if you drag from 'Organization'[Profit Center] to responsible'[Profit Center].

    In addition, the opposite relationship leads to a wrong result. Please correct your relationship and check if it is successful. More details about creating relationship, please review this article.

    Best Regards,
    Angelia

    Friday, October 21, 2016 9:04 AM
  • Hi okay, I was in doubt if it really was a many-to-many relationship as each of the lookup dimension tables only have unique values. It is only  the table 'Sales Responsible" where [Country Code], [Profit Center] and [Sales responsible] are unique in the combination of the three, but each can occur multiple times. But is it a many to many as I am connecting to two tables?
    Tuesday, October 25, 2016 6:36 AM
  • Hi Morten_DK,

    The relationship display actual meaning. Based on your description, it should be the one(click 'Sales responsible'[Profit Center]) to many('Organization'[Profit Center]) relationship.Please recreste the your relationship and check if it is sucessful.

    For Many-to-Many Relationshi, a database used by a school application can be taken as an example. Two of the tables it contains are "Student" and "Subject." In real life, a student will take several subjects simultaneously, while a subject will be studied by several students at a time. This is a many-to-many relationship.

    Best Regards,
    Angelia
    Tuesday, October 25, 2016 8:03 AM
  • Morten_DK,

    I tried to edit my initial response, but messed it up.  In order to obtain the filter propagation from the 'Sales Responsible' table to the 'Sales Fact' table, you need to include the 'Sales Responsible' table in the filter context.

    Sum of Revenue:= CALCULATE(SUM('Sales Fact'[Revenue]),'Sales Responsible')

    • Marked as answer by Morten_DK Tuesday, October 25, 2016 8:44 AM
    Tuesday, October 25, 2016 8:25 AM
  • Hi Bertrand,

    Thanks that worked :-) - it does howerver complain that it would like a relationship to be created once I use the Pivottable, but I suppose that can be ignored as Dax is handling it?

    Tuesday, October 25, 2016 8:44 AM
  • It is not completely clear to me what situations cause this warning to appear. When I feel confident that what I am doing is correct, I simply ignore it. If you click on create, and no relationship is created, the warning goes away.
    To go further, you may look at this post. Hoping it will help.
    Tuesday, October 25, 2016 10:41 AM