Many to many with a slight twist

[Joe_S]

I have a situation with a many-to-many relationship that works fine, but a new wrinkle was introduced that I am struggling with.

The following measure works fine:
MyMeasure = CALCULATE(SUM(FactTable(Amount),BridgeTable)

However, I need to extend it by the multiplier column in the bridge table below. For my specific need, if the tomato category is fruit, I use the actual value, but if the category of tomato is a vegetable I would like to multiply it by -1 in my measure.


Thanks in advance.

BridgeTable

Food	Category	Multiplier
Tomato	Fruit	1
Tomato	Vegetable	-1

 

[earthworm]

Try this

Book1
	A	B	C
1	Food	Category	Multiplier
2	Tomato	Fruit	1
3	Tomato	Vegetable	-1
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(AND(A2="Tomato",B2="Fruit"),1,IF(AND(A2="Tomato",A2="Vegetable"),-1,""))
C3	C3	=IF(AND(A3="Tomato",B3="Fruit"),1,IF(AND(A3="Tomato",B3="Vegetable"),-1,""))