Forum Discussion
Simplify join/add column operation
I have been wrestling with these tables for so long. The only way I can manage to join the data together is to define them as separate tables first and then use ADDCOLUMNS to lookup from the other table. However I really want to reduce clutter and minimize the number of total defined tables since this dashboard will be including a lot of different prices and will necessitate many tables already. Does anyone have any ideas of how I can combine these tables into less formulas and overall tables? I tried using NATURALLEFTOUTERJOIN and ADDCOLUMNS and VARs and they all gave me errors.
It is a many to one join where I need all the rows in ForecastPrices, which has repeating Month values, and lookup the unique Month row from MarketPrices.
JoinedTable =
ADDCOLUMNS(
ForecastPrices,
"Price",LOOKUPVALUE(MarketPrices[Price],MarketPrices[Month],ForecastPrices[Month])
)
Sample output of JoinedTables:
MarketPrices = SUMMARIZE(
FILTER(
HOEPMarketPrices,
HOEPMarketPrices[Date] >= DATE(2022, 3, 1)
),
HOEPMarketPrices[Month],
"Price", SUM(HOEPMarketPrices[Product]) / SUM(HOEPMarketPrices[OntarioDemand])
)
ForecastPrices = SUMMARIZE(
'public HOEPForecast',
'public HOEPForecast'[Month],
'public HOEPForecast'[createdAt],
"Forecast Date",'public HOEPForecast'[createdAt],
"Forecast Price",AVERAGE('public HOEPForecast'[HOEPForecast])
)
Examples of errors:
Hi cbirch
You can try the following.
JoinTable = VAR MarketPrices = SUMMARIZE ( FILTER ( HOEPMarketPrices, HOEPMarketPrices[Date] >= DATE ( 2022, 3, 1 ) ), HOEPMarketPrices[Month], "Price", SUM ( HOEPMarketPrices[Product] ) / SUM ( HOEPMarketPrices[OntarioDemand] ) ) VAR ForecastPrices = SUMMARIZE ( 'public HOEPForecast', 'public HOEPForecast'[Month], 'public HOEPForecast'[createdAt], "Forecast Date", 'public HOEPForecast'[createdAt], "Forecast Price", AVERAGE ( 'public HOEPForecast'[HOEPForecast] ) ) RETURN ADDCOLUMNS ( ForecastPrices, "Price", MAXX ( FILTER ( MarketPrices, [Month] = EARLIER ( 'public HOEPForecast'[Month] ) ), [Price] ) )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi cbirch
Based on your descriotion and the data, After treating these two tables as separate entity tables, it is possible to use the lookupvalue() formula.
Column = LOOKUPVALUE(MarketPrices[Price],MarketPrices[Month],ForecastPrices[Month])Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, I am already using that formula. I'm asking about a way to avoid defining them as separate entity tables in the first place. I want to define ForecastPrices, MarketPrices, and JoinedTable together within one expression. I can't just substitute in the current formulas, because it throws the error "Table variable cannot be used in current context because a base table is expected". Do you know of any other way to do this without defining separate tables?
Hi cbirch
You can try the following.
JoinTable = VAR MarketPrices = SUMMARIZE ( FILTER ( HOEPMarketPrices, HOEPMarketPrices[Date] >= DATE ( 2022, 3, 1 ) ), HOEPMarketPrices[Month], "Price", SUM ( HOEPMarketPrices[Product] ) / SUM ( HOEPMarketPrices[OntarioDemand] ) ) VAR ForecastPrices = SUMMARIZE ( 'public HOEPForecast', 'public HOEPForecast'[Month], 'public HOEPForecast'[createdAt], "Forecast Date", 'public HOEPForecast'[createdAt], "Forecast Price", AVERAGE ( 'public HOEPForecast'[HOEPForecast] ) ) RETURN ADDCOLUMNS ( ForecastPrices, "Price", MAXX ( FILTER ( MarketPrices, [Month] = EARLIER ( 'public HOEPForecast'[Month] ) ), [Price] ) )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
