Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nawt
New Member

Generating Data Model Table Contents Power Pivot

I am a Power Pivot beginner and I created the following data model: Example 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?

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.