Generating Data Model Table Contents Power Pivot

[nawt]

I am a Power Pivot beginner and I created the following data model:

Contained in the following Excel: Example excel file

The tables productParts, productList and productProduction contain data. Now I am trying to create the partProduction table that contains for each weeknumber the production amount. The weekly part production amount equals the part product percentage (from productParts) times the corresponding weekly product production from table product Production. I tried a few DAX functions for generating the columns in partProduction but I cannot get it to work. Any ideas on how to approach this problem?

[Anonymous]

Hi @nawt,

If you switch the cross filter direction to "both", you can simply use related function to find out the related "part name".

Sample:

Part production= SelectColumns(AddColumns(Productproduction,"Part Name",Related(productparts[Part Name])),"Part Name",[Part Name],"Week Number",[Week Number],"Production Amount",[Production Amount])

In addition, you can also use lookupvalue function to find out the related "part name", then use the new column and original table to create the part production table.

Calculate column:

Part Name= Lookupvalue(productParts[Part Name],productParts[Product Name],productProduction[Product Name])

Regards,

Xiaoxin Sheng

[nawt]

Since I need to do post processing in excel I am using the excel power pivot instead of BI. It seems that power pivot does not allow the cross filter direction to be set to both.

[Anonymous]

Hi @nawt,

Since you mentioned your issue are more related to use dax in excel pivot table ,perhaps you can post this requirement to pivot forum for better support.

Regards,
Xiaoxin Sheng