Trying to account for one category of values across two distinct groups?

[Abraxus]

Hi All,

I’m trying to do a comparison chart and hitting something of a brick wall (though it feels like it should be a minor issue). Basically, the aim is for the client to be able to select a single country from a list/slicer, and then have the chart display that countries value/rating in a group relative to the region. I’ve got everything working okay except for one very annoying snag, the US needs to sit in both the Latin America and Europe regions (not my choice but the client insists). I can’t share the main data table due to sensitivity etc., but simply put it has all of the countries in one column, with the regions in another (that I created using a calculated column in M Query), score ratings and so on.

Beyond that though, I’m using DAX tables/queries to make the slicer/data work as intended. Examples below:

The chart and the main slicer (other slicers are working okay: risk category [single selection only] and date slider). I’ll put a proper title in place later, for now focused on getting the USA parameter to work. As you can see, it works as expected for the other countries (in this case, China).

But if I select a country in Latin America, the US is missing.

And likewise United States sits in its own group if selected (which is okay for now).

And the two hierarchy tables I’m using to manipulate the core data (country hierarchy table feeds into regional hierarchy table that then feeds into the chart [slicer is using the first table]).

Country hierarchy/slicer table

Regional hierarchy table

Clearly the Region entry for United States isn’t going to work as is, but I’m wondering if there’s a way around it using DAX? In summary, I need to account for the United States in two groups, but have it appear only once in the slicer (oh and I'm sure this isn't the best way to achieve the current state in any case, so any contrucstive criticism/advice on that front is also welcome). Thanks in advance for any help!

[Greg_Deckler · Accepted solution]

What about just having 2 rows for the United States, one for each region?

[Abraxus]

Thanks Greg,

As those tables (and thereafter, colums) are built via DAX unfortunately it doesn't work, as there's only one instance of the country, so even if I put two 'rows' into the DAX, it only accounts for the first instance ( in this case Europe), for example:

Region = 
    IF(
        'Regional hierarchy table'[Country] = "Afghanistan", "APAC",
    IF(
        'Regional hierarchy table'[Country] = "China (mainland)", "APAC",
    IF(
        'Regional hierarchy table'[Country] = "Japan", "APAC",
    IF(
        'Regional hierarchy table'[Country] = "United Kingdom", "Europe",
    IF(
        'Regional hierarchy table'[Country] = "Germany", "Europe",
    IF(
        'Regional hierarchy table'[Country] = "Russia", "Europe",
    IF(
        'Regional hierarchy table'[Country] = "Somalia", "Africa",
    IF(
        'Regional hierarchy table'[Country] = "Kenya", "Africa",
    IF(
        'Regional hierarchy table'[Country] = "Angola", "Africa",
    IF(
        'Regional hierarchy table'[Country] = "Israel", "MENA",
    IF(
        'Regional hierarchy table'[Country] = "Turkey", "MENA",
    IF(
        'Regional hierarchy table'[Country] = "Saudi Arabia", "MENA",
    IF(
        'Regional hierarchy table'[Country] = "Venezuela", "Latin America",
    IF(
        'Regional hierarchy table'[Country] = "Brazil", "Latin America",
    IF(
        'Regional hierarchy table'[Country] = "Mexico", "Latin America",
    IF(
        'Regional hierarchy table'[Country] = "United States", "Europe",
    IF(
        'Regional hierarchy table'[Country] = "United States", "Latin America"
    )))))))))))))))))

Unless you're suggesting a new/physical non-DAX table in it's/their place?

[Abraxus]

Just to say I went ahead and replicated the regional hierarchy table as a straightforward/physical table (editable in Query editor etc.), and that's basically fixed it. Thanks for your response, don't know why I was sticking to using the DAX table when the result was so small anyway but appreciate you making me look at it differently.

Regards,

Abraxus.