Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Abraxus
Advocate II
Advocate II

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

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).

Screenshot 1.png

 

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

Screenshot 2.png

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

Screenshot 3.png

 

Regional hierarchy table

Screenshot 4.png

 

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!

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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"
    )))))))))))))))))

Screenshot 5.png

 

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

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.