Simplify join/add column operation

[cbirch]

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:

[Anonymous]

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.

[cbirch]

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?

[Anonymous · Accepted solution]

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.