Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |