How to use filter selections in a measure

[Rabin25]

Hi guys, I am working on a measure that needs to use filter selections to calculate inventory. The model I have has three tables Branch, Hub, and Inventory. Hub and Branch are filters and Inventory has Qty column that I want to create a measure with. There are more than 40 branches, and some of those branches can also be considered as Hubs. 

Branch Table:

BranchID	BranchName
1	Abc
2	xyz
4	mno
5	pqr

Hub Table:

HubID	BranchName
1	Abc
15	sse
25	eez
90	rpq

Inventory:

BranchID	Qty
1	15
2	25
4	50
5	10
15	300
18	20

I am working on a measure called Available_Territory where it needs to calculate the sum of quantities in the Inventory table based on what Branches and Hubs are selected in the filters. 

For example, if I have the following selections in the filters - 
Hub selection = 1,15
Branch selection = 1,2,5,18

Then the measure should calculate the sum of inventory quantity for the selected Hub and Branch IDs.
So the Available_territory = Calculate(SUM(Inventory[Qty]), FILTER(ALL(Inventory[BranchID]), Inventory[Qty] IN {1,15,2,5,18})
I tried capturing the filter selection using both SELECTEDVALUE() and ALLSELECED(), and putting them in a CALCULATETABLE() to use it in the measure, but it won't work. 

Here is the relationship model. I have not joined Hub to Inventory table as I want to use it just to capture HubIDs (which is same as branchid) and filter it in the Inventory[BranchID].

[elitesmitpatel]

Yes @lbendlin  is right just apppend or combine  Hub and Branch tables into a single table or if possible merge Inventory also it will sort all your problems

[Rabin25]

Fyi, I found a solution for this. 

I captured the values selected in the slicers using ALLSELECTED() and assigned to a variable. I used that variable to create a list to capture multiple selection using CALCULATETABLE() and assigned to another variable X. 

Then I used the variable X to filter the fact table (Inventory) on Branch or Hub column. 

[Anonymous]

Hi @Rabin25 ,

Thanks for the reply from @lbendlin .

Was the method lbendlin provided helpful? Did it help you solve the problem?

If yes, please accept his answer as the solution, which will be of great help to users who encounter similar problems as yours.

If you have any further questions, please feel free to contact me.

Best Regards,
Yang
Community Support Team

[Rabin25]

Hub can also be a branch. For example BranchID 1 and HubID 1 are same location. There are only handful of Hubs but there are 40+ branches. HubID and BranchID both can be used to filter on Inventory[BranchID] because both of them are location ids. I need to find a way to combine the selected values in Hub and Branch filter, and use them to calculate inventory quantity for the selected hub and branch. 

[lbendlin]

Combine Hub and Branch tables into a single table, add a column indicating location type.

[lbendlin]

Why would the Hub table have a "BranchName"  column?