Calculate one Rowbased DAX Measure from two virtual Tables

Hi there.

I had to create a quick model and changed the table names so please "undo" them to fit your tables' names.  Here's the code and it does work now.

My Measure = 
VAR __onePitASelected= HASONEFILTER( 'Calendar A'[date] )
VAR __onePitBSelected = HASONEFILTER( 'Calendar B'[Date] )
var __shouldCalculate = __onePitASelected && __onePitBSelected
VAR __pitAProducts = VALUES( Products[Product] )
VAR __pitBProducts =
	CALCULATETABLE(
		VALUES( Products[Product] ),
		USERELATIONSHIP( Products[Date], 'Calendar B'[Date] ),
                -- We have to take off any filters that exist
                -- on 'Calendar A' because these are two different
                -- calendars and activating the second relationship
                -- does NOT deactivate the first one because the
                -- relationships refer to TWO DIFFERENT TABLES, not
                -- the same one.
                ALL( 'Calendar A' )
	)
var __productsInBoth =
	INTERSECT(
		__pitAProducts,
		__pitBProducts
	)
var __avgAcrossProducts =
	AVERAGEX(
		__productsInBoth,
		var __priceA = CALCULATE( MAX( Products[Price] ) )
		var __priceB =
			CALCULATE(
				MAX( Products[Price] ),
				USERELATIONSHIP( Products[Date], 'Calendar B'[Date] ),
                                ALL( 'Calendar A' )
			)
		return
			__priceB - __priceA
	)
return
	if( __shouldCalculate, __avgAcrossProducts )

The lesson to take from this is that when you want your question answered quickly and correctly it's best to supply a file with some example data and formulas so that others have something to work with and can see a tangible and concrete model.

By the way, is it necessary to have one of the relationships disabled? You could leave it as active and the model would still work OK (but you'd need to make some small changes to the code above, of course).

Best

Darek