Calculating Transfers To and From Locations based on the Selected Location

[Nahum]

Greetings!

I've got a table of units that are, on occasion, transfered between different locations. I'd like to have a nice and easy dashboard that can show me how many units are coming from and going to different locations. In order to facilitate help, I've included screenshots, code examples, and the linked example .pbix file.

My main datatable is a big list of transfers. These transfers take the form of:

[Unit ID, the previous location ID, A date code indicating when the unit left, the new location id, and the date code for when the unit was receieved]. 

The datecodes are irrelavant in the sample model, but they are used to differenchiate otherwise "duplicate" rows--its not uncommon for the same unit to be transfered between two locations several times. 

I'm having trouble working out the correct order and application of filters to get my transfer table working with selections, rather than hard coded values. Below you can see a sample table that includes a hard coded mockup for my Fanghorn location, as well as the faulty measure that would work off of the selected row (also Fanghorn). 

Current and Desired Outputs

I want to have a list of my locations and see how many units are going to and coming from them. Additionally, I want to be able to select a specific location to see where these units came from and where they are going. I have hardcoded my intended behavior for Fanghorn to demonstrate what I mean, because I'm stuck on how to implement the selection behavior. 

Below is my example datamodel. There's my main transfers table, a locations table, and two hardcoded tables that demonstrate my intended behavior for the Fanghorn location. Because the transfer tables have 2 location ids and neither is more obviously the "correct" one, I marked both as passive to force measures to specify which one they're using.

Current Datamodel

I have two sets of hard coded measures:

1. The first uses two DAX generated tables in order to make it super clear and visible what I'd like the final result to be
2. The second uses virtual tables and TREATAS and looks like how I expect the final version to be. 

My hardcoded tables look like this:

To Fanghorn = 
CALCULATETABLE ( FILTER ( Transfers, Transfers[New Location Id] = 23 ) )
// 23 being Fanghorn's location id

And their respective measures look like this:

Units Going To Fanghorn = 
CALCULATE (
    COUNTROWS ( 'To Fanghorn' ),
    USERELATIONSHIP ( Locations[Location Id], 'To Fanghorn'[Previous Location Id] )
)

My virtual measures which I hope resemble the solution look like this:

Virtual To Fanghorn = 
VAR adjustedTable =
    CALCULATETABLE (
        FILTER ( Transfers, Transfers[New Location Id] = 23 ),
        // 23 still being Fanghorn's location id
        TREATAS ( VALUES ( Locations[Location Id] ), Transfers[Previous Location Id] )
    )
RETURN
    COUNTROWS ( adjustedTable )

My currently busted virtual "To Selected" function looks like this:

Virtual To Selected = 
// This is (and its corresponding "From" companion) the function I'm having trouble with. I want to be able to generate these for the selected row on the fly. 
VAR adjustedTable =
    CALCULATETABLE (
        FILTER ( Transfers, Transfers[New Location Id] = [Selected Location Id] ),
        TREATAS ( VALUES ( Locations[Location Id] ), Transfers[Previous Location Id] )
    )
RETURN
    COUNTROWS ( adjustedTable )

I presume it's a scoping problem, but I can't seem to find a combination of ALL, ALLSELECTED, etc., that provides the intended behavior. 

I've included a onedrive link to .pbix of the example project here.

Any help or support would be lovely,

Thanks!

[johnt75 · Accepted solution]

I don't think you'll be able to get everything into the same table visual as there is no way to tell which row is selected - when you click on a location it doesn't refresh the table visual with new filters, the change is purely visual.

You could create a 2nd version of the locations table, not linked to anything, and then create measures like

Virtual From = VAR PrimaryLocation = SELECTEDVALUE( 'Locations'[Location Id])
VAR SecondaryLocation = SELECTEDVALUE( 'Locations 2'[Location Id])
VAR NumFrom = CALCULATE(
	COUNTROWS( 'Transfers'),
	TREATAS( { ( PrimaryLocation, SecondaryLocation)},
		'Transfers'[Previous Location Id],
		'Transfers'[New Location Id]
	)
)
RETURN NumFrom

Virtual To = VAR PrimaryLocation = SELECTEDVALUE( 'Locations'[Location Id])
VAR SecondaryLocation = SELECTEDVALUE( 'Locations 2'[Location Id])
VAR NumTo = CALCULATE(
	COUNTROWS( 'Transfers'),
	TREATAS( { ( PrimaryLocation, SecondaryLocation)},
		'Transfers'[New Location Id],
		'Transfers'[Previous Location Id]
	)
)
RETURN NumTo

and put these in a second table visual along with the column from the locations duplicate.

[Nahum]

Thank you for the clear and concise explaination! It's dissapointing that selecting a row in the table is purely visual, but your offered solution is perfectly acceptable. Thank you for pointing me in the right direction!