locked
Relationships between tables in power pivot data model. RRS feed

  • Question

  • Hello, I'm very confused regarding table relationships in power pivot data model in excel 2016.

    Let me give you an example.

    I have a facts table with lets say 4 categories (columns) and some sales values (one column)

    I want to link a lookup table with one more category and a foreign key one of the categories of the facts table. 

    If I make a new flat table (outer join) everything is fine.

    When I link those tables and try to make a pivot table without the sales values then the new category from the lookup table is connected with every other category from the fact table (It should have only 3 values from one category and has all the values from every category). When I add the values column in my pivot table then everything is fine and the new category from the lookup table has only the 3 values from the other category and not all the values)

    What I'm missing here?

    Saturday, October 28, 2017 11:28 PM

Answers

  • Hi Andreas,

    Thanks for your response.

    Currently, Power Pivot data model queries with dimensions only will disregard Model's  relationships and give a result of cross join, unless a measure is Included .

    If you want to select only dimensional value from the model, you can use RELATED function to bring in all of the related fields.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, October 30, 2017 3:13 AM

All replies

  • Sorry cant understand the issue. Can you post some sample data and explain? That should make it more clear

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, October 29, 2017 4:59 AM
  • Ok let me give you an example

    Here is a fact table

    Main Category

     SubCategory

     SubSubCategory

     Sales

    a

    a

    a

    160

    a1

    b1

    c1

    10

    a1

    b2

    c4

    40

    a1

    b3

    c7

    70

    a1

    b2

    c2

    100

    a1

    b1

    c5

    130

    a2

    b4

    c2

    20

    a2

    b5

    c5

    50

    a2

    b6

    c8

    80

    a2

    b4

    c3

    110

    a2

    b4

    c6

    140

    a3

    b7

    c3

    30

    a3

    b8

    c6

    60

    a3

    b7

    c1

    90

    a3

    b8

    c4

    120

    a3

    b8

    c7

    150

    Here is a Lookup table


    NewCategory

      SubSubCategory

    D1

    c1

    D1

    c4

    D1

    c7

    D2

    c2

    D2

    c5

    D2

    c8

    D3

    a

    D3

    c3

    D3

    c6

    And here is pivot table with sales which is right  

    NewCategory

      SubCategory

      Sum of Sales

    D1

    480

    b1

    10

    b2

    40

    b3

    70

    b7

    90

    b8

    270

    D2

    380

    b1

    130

    b2

    100

    b4

    20

    b5

    50

    b6

    80

    D3

    500

    a

    160

    b4

    250

    b7

    30

    b8

    60

    Grand Total

    1360

    And here the same table without sales where the categories is wrong

    NewCategory

      SubCategory

    D1

    a

    b1

    b2

    b3

    b4

    b5

    b6

    b7

    b8

    D2

    a

    b1

    b2

    b3

    b4

    b5

    b6

    b7

    b8

    D3

    a

    b1

    b2

    b3

    b4

    b5

    b6

    b7

    b8


    Why every value of Subcategory has been assigned to every value of NewCategory? What Am I missing here? Shouldn't  the second pivot table be the same as the first one, just without the sales value?



    Sunday, October 29, 2017 8:56 PM
  • Hi Andreas,

    Thanks for your response.

    Currently, Power Pivot data model queries with dimensions only will disregard Model's  relationships and give a result of cross join, unless a measure is Included .

    If you want to select only dimensional value from the model, you can use RELATED function to bring in all of the related fields.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, October 30, 2017 3:13 AM
  • Thank you Wilson, for your response.

    Do you know if there is any road-map to fix this issue? I mean this way we lose the meaning of relationships and connected tables.

    Monday, October 30, 2017 12:16 PM