TREATAS not picking a single value

[OnlyPhilip]

Hi everybody, I need your help for this apparently simple problem.

 SUMX(Batch,
        CALCULATE(
	    MAX(MetalValues[Value]),
            TREATAS(VALUES(Batch[Metal]), MetalValues[Metal]),
            TREATAS(VALUES(Batch[Date]), MetalValues[Date])
        )
 )

The Batch table contains a list of production Dates of different Metals. I need to retrieve the Price for those dates and metals from the unrelated MetalValues table and sum them up.
The issue is that, at each iteration of SUMX, the MAX function returns the max value of the whole MetalValues table. My expectation and need is that it only returned the MetalValue value from that Date and Metal (which is unique being a daily trading value).
Any idea on what's my mistake here?
Thanks
Fil

[OnlyPhilip]

Thanks for your answers. The original pbix contains some confidential data, I'm attaching  a screenshot of the two tables, hope it helps. The first one is the Batch table, the second it MetalValues, where the values should be retrieved from.

I thought maybe there was a mismatch in the Date field, but they both appear formatted as Dates.

Thanks!

Batch

MetalValues

[mahoneypat · Accepted solution]

Please try this measure expression instead

New Measure =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE ( Batch, Batch[Metal], Batch[Date] ),
        "@value",
        VAR thisdate = Batch[Metal]
        VAR thismetal = Batch[Metal]
        RETURN
            CALCULATE (
                MAX ( MetalValue[Value] ),
                MetalValue[Metal] = thismetal,
                MetalValue[Date] = thisdate
            )
    )
RETURN
    SUMX ( summary, [@value] )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@OnlyPhilip ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[AntrikshSharma]

Can you share the pbix file?