Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Nahum
Frequent Visitor

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 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 OutputsCurrent 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 DatamodelCurrent 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!

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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.

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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
Frequent Visitor

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.