Forum Discussion
Solved
How can I create a calculator that pulls values from two table visuals?
I have a data table that breaks down different mock food products by their recipes and the percent contribution of each ingredient to each the recipe. Here is a snippet of what this table looks like ...
Hi cs7238,
I was able to reproduce your scenario using sample data and achieved the expected similarity calculation.
I’ve attached the PBIX file for your reference
Thank you.
.
Measures never pull values from visuals, only from the data model and filter context. So even though you have two table visuals, you are still filtering the one data table. For the filter context, if the two slicers are using the same column from the data table, they form an AND condition. What you could do is create two disconnected tables for the two slicers with the spec codes and descriptions and use DAX to get the values you need. So the model would look something like this:
And the DAX measures something like this:
DEFINE
MEASURE 'Spec Code 1'[Percent Contribution Spec 1] = VAR productspec = SELECTEDVALUE('Spec Code 1'[Product Spec Code])
VAR spec = SELECTEDVALUE('Spec Code 1'[Spec Description])
RETURN
CALCULATE(
MAX(Data[Percent Contribution]),
Data[Product Spec Code] = productspec,
Data[Spec Description] = spec
)
MEASURE 'Spec Code 2'[Percent Contribution Spec 2] = VAR productspec = SELECTEDVALUE('Spec Code 2'[Product Spec Code])
VAR spec = SELECTEDVALUE('Spec Code 2'[Spec Description])
RETURN
CALCULATE(
MAX(Data[Percent Contribution]),
Data[Product Spec Code] = productspec,
Data[Spec Description] = spec
)
MEASURE 'Data'[% Similarity] = VAR A = [Percent Contribution Spec 1]
VAR B = [Percent Contribution Spec 2]
RETURN
DIVIDE( 2 * ( A * B ), ( A + B ) )