Invert 2 Filters

[Vigul]

Hello everyone, I`d like to ask for help with 1 interesting issue:

We have following table (for example)

CountryOrigin (Data Type=Text)	CountryDestination (Data Type=Text)	Volume FROM CountryOrigin	Volume TO CountryOrigin
FR-01	DE-32	82
DE-32	FR-01	144
DE-32	FR-01	10

We have 2 slicers where we select point for CountryOrigin and CountryDestination. When we select FR-01 in CountryOrigin we have DE-32 and FR-45 on CountryDestination displayed and we select DE-32. We get info that we have 82 cars going from FR-01 to DE-32.

The issue is, we want also to get data (during same selection) on how many Trucks we have FROM DE-32 back to FR-01. Meaning that when we select FR-01>>>DE-32 we get Volume FROM CountryOrigin 82 and also second column with Volume TO CountryOrigin 154 (144+10) displayed.

We tried doing measure like this:
Volume from-TO =
VAR FromTo =
FILTER('Table', 'Table'[CountryOrigin]=SELECTEDVALUE('Table'[CountryDestination]) && 'Table'[CountryOrigin]=SELECTEDVALUE('Table'[CountryOrigin]))
RETURN
CALCULATE(SUM('Table'[Volume FROM CountryOrigin]), FromTo)

But this measure results in empty values. Also we don`t have access to Queryeditor so it`s not possible to do anything from it.

Does anyone have any ideas how we can do this inverted selection, or is it even possible without query editing? 

[johnt75 · Accepted solution]

You could create 2 new dimension tables for use on the slicers, e.g.

From Slicer =
SELECTCOLUMNS(
	DISTINCT(
		UNION(
			DISTINCT( 'Table'[CountryDestination] ),
			DISTINCT( 'Table'[CountryOrigin] )
		)
	),
	"Country", 'Table'[CountryDestination]
)

To Slicer =
SELECTCOLUMNS(
	DISTINCT(
		UNION(
			DISTINCT( 'Table'[CountryDestination] ),
			DISTINCT( 'Table'[CountryOrigin] )
		)
	),
	"Country", 'Table'[CountryDestination]
)

Create an active relationship from each table to the appropriate column on your fact table. Then create an inactive relationship from each table to the other column. You can then create a measure like

Volume to country origin =
CALCULATE (
    SUM ( 'Table'[Volume from country origin] ),
    USERELATIONSHIP ( 'Table'[Country origin], 'To slicer'[Country] ),
    USERELATIONSHIP ( 'Table'[Country destination], 'To slicer'[Country] )
)