Filter to show branch and data from other branches in same region

[DanielLOOP]

Hi guys,

I am still pretty new in Power Bi, and this issue may be very simple, so I hope you are able to help min with this issue.

I want to create a page in Power Bi where I can select a branch and see there current revenue of the day, but at the same time I want to be presented with a list of the revenues from other branches in the same region.

Are there a way to filter a list of branches where the region is equal to the the branch you have chosesen?

Thank you in advance.

[v-rzhou-msft · Accepted solution]

Hi @DanielLOOP 

Firstly your measure will show error due _Region will only return to value instead of a list. So Center[REGION] in _Region is not correct you should use "=" instead of "in". And this measure will only calculate the sum of revenues in same region as the brachch you select.

Measure = var _Region = MAXX(ALLSELECTED(Center),Center[REGION]
return
calculate([All Members],filter(all(Center),Center[REGION] in _Region

My Sample:

Calculate the revenue by all member measure.

All Members = SUM(Center[Revenue])

You can try to build an unrelated slicer table to achieve your goal.

Slicer = VALUES('Center'[Branch])

Then build a measure:

Measure = 
VAR _Branch = SELECTEDVALUE(Slicer[Branch])
VAR _Region = CALCULATE(MAX(Center[Region]), FILTER(ALL(Center),Center[Branch] = _Branch))
VAR _REVENUE = IF(ISFILTERED(Slicer[Branch]),IF(MAX(Center[Region]) = _Region,[All Members],BLANK()))
RETURN
_REVENUE

Result:

If we select Branch1 it will show all values in same Region.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@DanielLOOP , Try a measure like

measure =
var _region = maxx(allselected(Branch), Branch[region]) //assuming you have branch table
return
calculate(sum(Table]), filter(all(Branch) ,Branch[region] in _region))

This will work better if barch is an independent table 

measure =
var _region = maxx(allselected(Branch), Branch[region]) //assuming you have branch table
return
calculate(sum(Table]), filter(Table ,Table[region] in _region))

[DanielLOOP]

Hi I have tried to make thise measure, but I get an error message. Is that becouse the revenue is calculated from another measure?

Measure = var _Region = MAXX(ALLSELECTED(Center),Center[REGION]
return
calculate([All Members],filter(all(Center),Center[REGION] in _Region