Forum Discussion
Calculating Transfers To and From Locations based on the Selected Location
- 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 NumToand put these in a second table visual along with the column from the locations duplicate.
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.
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!