Forum Discussion

Nahum's avatar
Nahum
Frequent Visitor
3 years ago
Solved

Calculating Transfers To and From Locations based on the Selected Location

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 ...
  • johnt75's avatar
    3 years ago

    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.