Forum Discussion
In Power Pivot, rows with erroneous data appear
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
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]
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 |