Filter a row from more than one column

[Anonymous]

Hi, I have a data structure with cities, countries and country groups, from which I create a hierarchy filter.

There is a value associated with each of these levels. For instance "% of population that is under 16". This cannot be calculated in Power BI because it needs to be weighted, and I don't have the weights. So instead, I have been provided with a list of all of the values. This means that Cities, Countries and Country Groups are all in one column (Location). 

I need to use the hierarchy filter on this location column to show one value at a time. But as I am joining the tables with just one column (let's say city), I don't see how this can work. For now, I have made them into separate tables. So I have a city table, a separate country table, (and a country group table eventually). Then I take my hierarchy filter table and join them separately. Then create a measure that switches between the tables based on how many cities are in the selection. This works but is cumbersome as it results in a lot of tables.

Is there a way to simplify this? 

[Anonymous]

Thanks, I guess it's good to know I'm not missing something obvious. I don't know what other format they could supply the data in, and I don't want to be calculating these aggregates in Power BI as they are very complex. So I am going with the method above where I essentially have three tables, and a measure to switch between the correct values in each case. Luckily only three measures (so far) 😅

[lbendlin]

As you have noticed your data is not in a usable format.  Reject the assignment and work with the requestor to make them understand the limitations of Power BI (especially the need for usable data).