Forum Discussion

cbirch's avatar
cbirch
Frequent Visitor
2 years ago
Solved

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 ta...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

cbirch's avatar
cbirch
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.