Forum Discussion
Conditional join on multiple columns
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?
6 Replies
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
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
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.randeep , you do not have a stock status table?
Turns out, simple LOOKUPVALUE was sufficient here.
LOOKUPVALUE('bi NetForecastFile'[$COGS],'bi NetForecastFile'[ConsolidateKey], 'bi StockOnHand'[ConsolidateKey], 'bi NetForecastFile'[Date], 'bi StockOnHand'[Date])
