filtering data, keeping some options always selected

[TA9]

Hi,

I am using the Census 2021 data and have created a file with ward, regional towns, region and England level data in order to compare the different geographical areas. All the geographical areas are in the same field (but I can change the underlying data format if needed)

My aim is to keep England, region, and town static and have a filter so the user can select a ward and the visual (e.g. bar graph) only updates the ward information.  In Excel, I could create a table of England, region and town data as static and then have a vlookup for the ward level data to change based on the filter... how can this be replicated in Power BI?

For example:

Deprivation	England	Region	Town	Ward Selected (This will change)
Does not apply	0	0	0	0
Household is deprived in four dimensions	54970	1973	128	4
Household is deprived in one dimension	7842690	394780	20394	811
Household is deprived in three dimensions	868104	53831	3517	35
Household is deprived in two dimensions	3320578	190800	10816	258
Household is not deprived in any dimension	11349743	534296	25407	1326
Grand Total	23436085	1175680	60262	2434

Any ideas?

Thanks.

[TA9]

Yes, so town, region and country will remain static and ward will change

[lbendlin]

Better test that UX with a real user. They may not like that.

[lbendlin]

My aim is to keep England, region, and town static 

So you will only ever have users from that one town?