Relationship issues in Datamodel and DAX Measure

Hi,

I do have issues with relationship in the datamodel to show the proper data in my analysis.
The attached sample file should show my problem.

I do have 7 Tables loaded into PQ and the Datamodel.
DIMCustomer: List of Customer and partnumber (=Finish Goods)
DIMSupplier: List of Supplier part number and the names
DIMDate: just simple years
SalesVolume: for each Customer Finish Good and year the sales volume
Sales Price: price per year for each Finish good
BOM: For each Customer Finish good a supplier partumber including the amount used for the finish good.
BOM Price: Price for each Supplier part for each year

BOM usage means e.g. to build Partnumber Customer 1 I need 5 S1 Part, 2 S2 Part and 1 S3 parts

I created the relationships but get the wrong results im my worksheet PIVOT.


What do I want:
The Pivot table should be able to show per my row selection the Salesvolumen, the used BOM Parts and quantity of them, the cost of the BOM parts,...

I created one example in the sheet PIVOT (Yellow area)


What is my problem:
The Relationship is not working. after selecting the cust.Partnumber I see all Suppliers and all Partnumber of the supplier. Therefore the volumes are also wrong

Another issue is the DAX to calculate the measure. E.g. for my selection in the pivot, multiply the USAGE with the Volume(=measure BOM Units), then take this measure and multiply the result with the price




I hope someone can help me out a bit


thanks

Hi
sorry, uploaded the initial file in my second post, just now changed to the correct file'
thanks

Hi, so the filter only propagate to the many side, not further down?

Does this mean I need to merge the tables in PQ to get back to a star scheme for my relations? I was trying to use the formula:
CustSalesPrice=RELATED(SalesVolumes[Volume]) to create a calculated column in the DimSupplier but doesnt work

Any ideas how I can move on?
thanks

hi, thanks for the reply,

I had a look and still, i am confused how to create the related formulas. I tried to do but get an error for mine
CustSalesPrice=RELATED(SalesVolumes[Volume])
This formula is invalid or incomplete: 'The column 'SalesVolumes[Volume]' either doesn't exist or doesn't have a relationship to any table available in the current context.'.

I will have a look over the weekend hopefully

thanks

Hi
took longer than expected but found a solution

I added a measure:

Please Login or Register  to view this content.

My understanding now is that calculate passes the filter from table BOM into the salesvolume table.


By doing so also for the other dimensions, I get my desired pivot table.

I will now go and integrate this solution into my real data and verify. But looks good so far.

thanks