In Power Pivot, rows with erroneous data appear

[PaiYa]

I have three table in Data Model, Salespersons, Products and Orders.

The relationship between three table is as below:

1. By SalespersonID: Salespersons (one) <= Orders (many)

2. By ProductID: Products (one) <= Orders (many)

When I use Power Pivot as below, if there is not field used in Values, it could not show the related item. (as Orders, Alice should own 101 and 103)

If there is field used in Values, it somehow could show the related item. (as Orders, distinct count of Alice's Product ID is 2, 101 and 103)

I would like to ask whether the data model fails to recognize the intended relationships in Rows and Columns when no fields are referenced in Values.

[shafiz_p · Accepted solution]

Hi @PaiYa  

Power Pivot in Excel and Power BI do indeed handle relationships and data modeling differently, which can lead to the behavior you're observing.

In Power Pivot, when you place SalespersonName and ProductID in the Rows area, it might show all Product IDs under each Salesperson if there are no fields in the Values area to enforce the relationships. This happens because Power Pivot performs a full outer join, displaying all possible combinations of Salesperson and Product ID.

To ensure that only the relevant Product IDs appear under each Salesperson, you need to use measure or field in the value field.

Hope this helps!!

If this solved your problem, please accept it as a solution and kudos!!

Best Regards,
Shahariar Hafiz

[MattAllington]

Yes, they behave differently. To see what you see in Power BI while using Power Pivot, you need to add the measure I mentioned 

[MattAllington]

You must have a measure to leverage the relationships. If you have no measure, it simply lists all possible outcomes. If you simply did a countrows of orders and added it to values, it would show you the count of transactions in your order table. If you want tou count the products, then do a count of the Orders[product ID]

[PaiYa]

Thank you for your reply. My confusion is not about how to use measures, but rather about how Power Pivot interprets relationships (Excel seems to behave differently from Power BI). Based on Order, when I place SalespersonName and ProductID in rows, the data should appear as follows, rather than as shown in the image.

Alice

101

103

Bob

102

Charlie

101