Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |