Calculated Column based on UN-related Column

[damguy09]

I've used this forum for months, but this is my first time asking for help.

I am trying to make a calculated column in one of my related tables that uses an UN-related column as the lookup field. I have 2 fields that have product names. The 1st is my Product (key) and 2nd is a raw material. For some of my products, the raw material is also a finished good (product - key).

example: Product A --> Product B + Raw Material X

Product Name (key)	Raw Material Name	%of RM
Product A	Product B	80%
Product A	Raw Material X	20%
Product B	Raw Material Z	60%

<tbody>
</tbody>

I have another fact table (factProductionHours) that shows how long it took me to make each product [hrs]. These are both related to my dimProduct table. I would like to make a calculated column that shows how many hours were used to make a product that is also used as a RM.

My first idea was to do this outside of DAX in the linked table and just use SUMIF. This works except it won't apply the date filters on my factProductionHours table. In DAX if I use SUMX it pulls the production hours for the Product Name (key), not the Raw Material Name column.

If you need further clarification I will try to explain better. Thanks in advance.

 

[powerpivotpro]

I am not precisely following your problem, but I have an inkling that it's similar to this post:

Correlating “Fuzzy Time” Events in One Table with Precise Measurements in Another Table « PowerPivotPro

The post in question may be overkill for your needs but hopefully gets you on the right track.

 

[damguy09]

Thanks for your response. This is what I needed expect I'm having a little trouble with my time filter. I am able to pull over the correct information in the calculated column using calculate, but it doesn't work with my date slicer on the pivot table. Here's my dax:

=CALCULATE(SUM(Production_Hrs[Act_Hrs],

FILTER(Production_Hrs,​

Production_Hrs[RM_Name]=Build_Plan[RM_Name] &&
Production_Hrs[Production_Date]>=MIN(Calendar[Date]) &&
Production_Hrs[Production_Date]<=MAX(Calendar[Date])
)​

)
​

In plain English, what I would like to do is for the calculated column to SUM the production hours for the dates associated with my date slicer. When I use this dax it returns the whole Calendar table (not filtered to the slicer).

BTW, I really like your book and your talk at the PASS Analytics conference! Great stuff!

Thanks.

 

[powerpivotpro]

Ah! Calc columns NEVER respond to slicers. Meaning, the numbers IN the calc column are "set in stone." Slicers can impact WHICH rows get included in the measure calcs for the pivot of course, via the filter context stuff covered in chapter 7, but a given row's value for that calc column is static.

So maybe your formula should be used as a measure instead?

Glad you are enjoying the book and that you liked the PASS presentation. That talk was fun! Probably my favorite of all time.