Forum Discussion

cbirch's avatar
cbirch
Frequent Visitor
1 year 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
    1 year 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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    cbirch
    Frequent Visitor
    1 year 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
      1 year 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.