Text value from a column to filter another table

[OnlyPhilip]

Hi everybody,

I can't figure out how to retrieve the text value (the metal field in the example below) from a column to be used as a filter for another column, row by row, so to fill a visual table.

I have this two tables:

MetalValues, with the market value of different metals (here just two as an example) along the year:

Date	Metal	Value
1/1/2020	Iron	200
2/1/2020	Iron	199
1/1/2020	Copper	50
2/1/2020	Copper	51

Production, with the metals production of the day for the full year: 

Date	Metal	ProducedQuantity
1/1/2020	Iron	10
1/1/2020	Copper	50
2/1/2020	Iron	12
2/1/2020	Copper	60

The two tables are linked with an inactive relation between the Date columns, so that I can make use of USERELATIONSHIP to temporarily activate the relation and filter by date (it would create issues if I leave the relation active).

But I can't figure out how to also filter by metal so to have the correct multiplication.

I would like to have a visual table that shows Production and its values, like this:

Production[Date]	Production[Metal]	TotalValue
1/1/2020	Iron	2000
2/1/2020	Iron	2388
...

Given that I need to relate both by date and by metal, I assumed this could have worked:

TotalValue= 
  CALCULATE(
    MAX(MetalValues[Value])*MAX(Production[ProducedQuantity]),
    FILTER(MetalValues, MetalValues[Metal]=Production[Metal]),
    USERELATIONSHIP(Production[Date],MetalValues[Date])
  )

In the code above, USERELATIONSHIP actually retrieves the right dates from MetalValues, but I get an error on the FILTER that aims to also retrieve the right metal, because I'm providing a column (Production[Metal]) instead than a single value.

How shall I code this second filter on the metal value?

[mahoneypat · Accepted solution]

You can not need the inactive relationship at all if your use an approach using TREATAS() as follows:

Total Value =
VAR __qty =
SUM ( Production[Produced Quantity] )
VAR __metalvalue =
CALCULATE (
MIN ( MetalValues[Value] ),
TREATAS ( VALUES ( Production[Date] ), MetalValues[Date] ),
TREATAS ( VALUES ( Production[Metal] ), MetalValues[Metal] )
)
RETURN
__qty * __metalvalue

Or you could use USERELATIONSHIP for the date and just use TREATAS() on metal column.  Note: this assumes you are using the columns from Production in your visuals (for Metal and Date).

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

Regards,

Pat