Filter visuals based on slicer selection's parent category

[amos_jevons]

I want users to be able to select an item from a slicer and see a report for all items in the same category.

I have a table similar to this:

COUNTRY   | REGION         | POPULATION      | OECD_MEMBER
Australia | Oceania        | 24,000,000      | True
Austria   | Europe         | 8,747,000       | True
Belgium   | Europe         | 11,350,000      | True
China     | Asia           | 1,379,000,000   | False
Ecuador   | South America  | 16,390,000      | False
Fiji      | Oceania        | 898,760         | False 

Each REGION can contain multiple COUNTRIES but a country can only be in one region.

I want users to select an entry from the COUNTRY column and see the population of its REGION, stacked by OECD_MEMBER status. For instance, selecting AUSTRALIA would show a bar for OCEANIA with 96% under TRUE (representing Australia) and 4% under FALSE (representing Fiji). 

I have created a slicer for COUNTRY but I cannot figure out how to set a visual filter based on the selected country's REGION.

[v-yuta-msft · Accepted solution]

Hi amos_jevons,

To achieve your requirement, create a measure using dax formula below:

Result = 
VAR region = CALCULATE(MAX(Table1[REGION]), FILTER(ALL(Table1), Table1[COUNTRY] = SELECTEDVALUE(Table1[COUNTRY])))
VAR total = CALCULATE(SUM(Table1[POPULATION]), FILTER(ALL(Table1), Table1[REGION] = region))
return
CALCULATE(MAX(Table1[POPULATION]), FILTER(ALL(Table1), Table1[COUNTRY] = SELECTEDVALUE(Table1[COUNTRY]))) / total

 

Regards,

Jimmy Tao