Conditional join on multiple columns

[randeep]

I have following model, which are imported into my project.

I would like to either have COGS field from Stock On Hand into Net Forecast or vice versa, by connecting product and date fields.

The idea is to calculate a new measure which is [Net Forecast].COGS / [Stock On Hand].COGS

In SQL, the query will look something like : 

select SOH.Product, SOH.Location, SOH.Date, SOH.QUANTITY AS SOH_QTY, F.QUANTITY AS FORECAST_QUANTITY from  bi.StockOnHand SOH 
FULL OUTER JOIN bi.NetForecastFile F 
ON SOH.Product = F.Product  AND SOH.Location = F.Location AND SOH.Date = F.Date
WHERE SOH.Quantity >0

Since both models are already imported, and huge, I would like to eliminate importing another model from query.

Rather have a table extracted from these model or DAX query. This is what I created so far, but end up with lots of empty rows and duplication.

StockTurnAround = 
Var MergeTable = GENERATEALL('bi StockOnHand', SELECTCOLUMNS(FILTER('bi NetForecastFile', 'bi NetForecastFile'[ConsolidateKey] = 'bi StockOnHand'[ConsolidateKey] && 'bi NetForecastFile'[Date] = 'bi StockOnHand'[Date]), "N.Product", 'bi StockOnHand'[Product], "N.Location", 'bi StockOnHand'[Location], "N.Date", 'bi StockOnHand'[Date], "SOH COGS", 'bi StockOnHand'[$COGS], "Forecast COGS", 'bi NetForecastFile'[$COGS]))
VAR MERGE_CLEAN_DISTINCT =
    DISTINCT ( MergeTable )
RETURN
MERGE_CLEAN_DISTINCT

Where am I getting the query wrong?

[randeep]

Turns out, simple LOOKUPVALUE was sufficient here.

LOOKUPVALUE('bi NetForecastFile'[$COGS],'bi NetForecastFile'[ConsolidateKey], 'bi StockOnHand'[ConsolidateKey], 'bi NetForecastFile'[Date], 'bi StockOnHand'[Date])

[amitchandak]

@randeep , As long as you have common dimensions like product and Date and Facts like Stock on Hand and net forecast join to those as shown in the relationship in power BI,

You can create a new measure

New measure = divide(sum('Net Forecast'[COGS]) ,sum('Stock On Hand'[COGS]))

and should able analyze that across Product and dates and any other common dimension

[randeep]

@amitchandak  I wish it was that easy. 😀
You see there is no direct relationship established in between Net Forecast and Stock On Hand table. There is intermediarry table here, which is required too.

[amitchandak]

@randeep , you do not have a stock status table?

[randeep]

Yes, but the relationship between Stock Status and Net Forecast on Product field. And Stock Status and Stock On Hand on Product field.

The Date field is connected to Calendar table.

When I use you proposed formula, it just sums up entire table. 

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490