Hi everyone,
I hope I'm posting in the right place, if not, please let me know.
I have an optimization problem with my data entities to exploit them on PBI and would like to submit my idea to you.
I have two tables: InventItemPrice and BOMCalcTable. In InventItemPrice I have the cost prices of each items, and on my PBI, I'd like to display details of these calculations, which are in BOMCalcTrans. For example, I want to know the total of the RM and subcontracting costs per part. The problem is that doing this calculation with a measurement in PBI requires too much capacity.
So I'd like to create an InventItemPrice view with an additional column that would be the sum of the BOMCalcTrans rows corresponding to RM and subcontracting.
My first attempt seems unsuccessful. Here's the architecture with the Datasources:
But when I open the view via the table explorer, even though I haven't added any BOMCalcTrans fields and the relationship is in OneToOne, the InventItemPrice rows are still multiplied by the number of BOMCalcTrans rows returned by the relationship.
Hence my question: if I want to add to InventItemPrice the sum of several BOMCalcTrans lines returned by the relationship InventItemPrice.PriceCalcId = BOMCalcTrans.PriceCalcId, what's the best solution?
Thank you in advance for your advice.
