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.
Hi everyone, I have a slicer that has 'Country Group Total'[Zone] with 'Country Group Total'[Country] nested underneath. I'm trying to create a measure that will return the values selected in the slicer. If all countries within a zone(s) are selected, those zones will be concatenated together. If there is a zone that has some but not all countries selected, then those countries will be appended at the end.
As a shortened example:
Mexico, United States, and Canada are all countires in zone North America
Ireland, Scotland, Wales, and England are all countries in zone Great Britain
France, Germany, and Italy and are countries in zone Europe
If all countries in North America and Great Britain are selected, along with Germany, the result would be "North America, Great Britain, Germany"; if only Mexico and Germany are selected, the result would be "Mexico, Germany"
I was able to get it working with Zones but couldn't figure out a way to append the selected countries at the end. To be more specific, I'm having trouble solving for VAR _ConcatPartialZoneCountries. All help is greatly appreciated!
VAR _ZoneSelectedType =
ADDCOLUMNS (
SUMMARIZE ( 'Country Group Total', 'Country Group Total'[Zone] ),
"@ZoneSelectionType",
VAR _CountInZone =
CALCULATE (
COUNTROWS ( 'Country Group Total' ),
ALLEXCEPT ( 'Country Group Total', 'Country Group Total'[Zone] )
)
VAR _CountSelected = CALCULATE ( COUNTROWS ( 'Country Group Total' ) )
VAR _Switch =
SWITCH (
TRUE (),
_CountInZone = _CountSelected, "All",
_CountSelected > 0, "Some",
"None"
)
RETURN
_Switch
)
VAR _AllSelected = FILTER ( _ZoneSelectedType, [@ZoneSelectionType] = "All" )
VAR _SomeSelected = FILTER ( _ZoneSelectedType, [@ZoneSelectionType] = "Some" )
VAR _ConcatAllZones = CONCATENATEX ( _AllSelected, 'Country Group Total'[Zone], ", " )
VAR _ConcatPartialZoneCountries = "List of Countries"
VAR _Combine = _ConcatAllZones & ", " & _ConcatPartialZoneCountries
Solved! Go to Solution.
You could CONCATENATEX the countries, filtering by _SomeSelected Zones:
You could CONCATENATEX the countries, filtering by _SomeSelected Zones:
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |