Forum Discussion
Solved
Data model relations not working properly in power pivot
Hi when doing relations i had some strange results, although same when i did in power bi it worked
i have 2 tables of customers one with unique values & one with duplicates, so when i made the relation from unique customer ids to duplicate customer ids, then when i did the pivot table by using data model,
i used the customer ids from unique table & customer preferences from customer_preference table which contains duplicate ids, but it gave wrong results dont know why
If you have nothing in Values, PivotTable performs Full Outer join, i.e. all to all. As soon as you add any value relationships work.
11 Replies
It sounds like the issue stems from how Power Pivot handles relationships, especially when dealing with duplicates. Even though your unique customer ID table is correctly linked to the duplicate-rich preferences table, incorrect results can happen if the relationship isn’t set as one-to-many or if the filtering direction isn’t from the unique table to the duplicate one. In your case, using customer IDs from the unique table and pulling in preferences like "organic foods" from the duplicates table should work—just ensure the relationship is properly configured. Power BI might auto-correct some of these relationship issues, but Power Pivot needs manual accuracy.
jackjoxon , that's not the case here. You may check the file, all relationships are correctly defined. The case is PivotTable need to have some aggregation.
If you have nothing in Values, PivotTable performs Full Outer join, i.e. all to all. As soon as you add any value relationships work.
SergeiBaklan Thank you
- Your tip made my day! Thanks so much Sergei!
SergeiBaklan Hi, I have got the same situation and try to put some count as you have suggested but it still does not work. Do you know how can we handle this? Thanks you.
SergeiBaklan - what if I do not have values in my tables?
Sorry, I didn't catch. If you don't have any values in your source tables you actually have no such tables, Values could be texts, whatever. In PivotTable you need to aggregate something to have proper result, e.g. count number of such texts.
