Forum Discussion

amos_jevons's avatar
amos_jevons
Regular Visitor
7 years ago
Solved

Filter visuals based on slicer selection's parent category

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's avatar
    v-yuta-msft
    7 years ago

    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

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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