Forum Discussion

OnlyPhilip's avatar
OnlyPhilip
Frequent Visitor
6 years ago
Solved

Text value from a column to filter another table

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:

DateMetalValue
1/1/2020Iron200
2/1/2020Iron199
1/1/2020Copper50
2/1/2020Copper51

 

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

DateMetalProducedQuantity
1/1/2020 Iron10
1/1/2020 Copper50
2/1/2020 Iron12
2/1/2020 Copper60

 

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/2020Iron2000
2/1/2020Iron2388
...  

 

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's avatar
    mahoneypat
    6 years ago

    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

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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