Calculated column based on relationships

[Anonymous]

Dear users, I hope you can help me as you always have,

I want to calculate a column in one of my fact tables by taking data from another fact table and using existing common relationships with dimensions to get the right value.

My dataset is a bit complex but I try to simplify it.

Fact Table 1 (actual outcome):
[MonthKey],[ProdTypeKey],[Qty]

Dimension Table 1 - Connected to [ProdTypeKey]
[ProdTypeKey],[ProdForecastKey]

Dimension Table 2 - Connected to Dimension 1

[ProdForecastKey] (There are other fields here too obviously).

Fact Table 2 (forecast) - Connected to Dimension 2
[MonthKey],[ProdForecastKey],[Qty]

Dimension Table 3 - Connected to both fact tables
[MonthKey], [Year], [Quarter] etc....


The question is:
How can I create a calculated column in Fact Table 2 that gives me [Qty] from Fact table 1, also by applying the common relationship filters of [MonthKey] and either of [ProdTypeKey]/[ProdForecastKey] ? 

[AnthonyJoseph]

@Anonymous Unless its a many to many relationship you can try Lookup in calculated column. Ex:

Calculated column = Lookupvalue(Fact Table 1 [QTY],Fact Table 1[MonthKey],Fact Table 2[MonthKey])

[Anonymous]

Hi Anthony,

Thanks, but I am afraid it does not work for me.
I get the error message "A table of multiple values was supplied when a single value was expected".

Fact Table 1 is much more detailed than Fact Table 2. 

Any other suggestion? 

[AnthonyJoseph]

@Anonymous yes, definelty fact 1 table is more detailed than fact 2.
Can you try the below i.e. using ProdForecastKey ( which I assume has one to one relationship)

For this, first you should create a column in Fact Table 1

ProdTypeKey

Calculated column = Lookupvalue(Dimension Table 1 [ProdForecastKey],Dimension Table 1[ProdTypeKey],Fact Table 1[ProdTypeKey])

Once created, create the below column in the fact table 2:
Calculated column = Lookupvalue(Fact Table 1 [QTY],Fact Table1[ProdForecastKey],Fact Table 2[ProdForecastKey])

Approach 2: Find the maximum occuerences of the resulting column [QTY] from Table 1 and return that value as the result of lookup value in fact table 2.

Approach 3: Use Merge tables in Query editor to get the value from fact table 1.

Let me know if you got blocked.