Accessing Matrix hierarchy levels

DAX DAX Calculations
1

Hi,
I am not sure if anyone has faced issue with calculations in performing DAX calculations specifically at different hierarchy levels, I did. It was mainly due to the existing table structure in Power BI which I was not allowed to change. I thought of sharing it here, just want to know if there is any better option. Thanks
Let’s say we have 3 tables,
Table 1 - Single hierarchy table with “Country”, “States”, “City” (Fact Country)
Table 2 - Table with “Country” and “Population” (Country)
Table 3 - Table with “City” and “Population” (City)
We are trying to show the Population data in a Matrix having hierarchy levels Country and City. How do we identify, which table values to be summed up to show at either “Country”/ “City” level?
I used this Dax function to identify the levels “ISINSCOPE()”, this exactly tells which level we are looking at. Here is a sample formula

TotalPopulation = 
VAR CountryLevel = ISINSCOPE('Fact Country'[country])
VAR CityLevel = ISINSCOPE('Fact Country'[city])
RETURN
//CountryLevel & CityLevel
IF(CityLevel, SUM(City[population]),
    IF(CountryLevel == TRUE() && CityLevel == FALSE(), SUM(Country[TotalPopulation]))
)

1

1 Like
2

Can’t you just simplify this a little bit? If all of the cities populations add up to the country populations then in theory you don’t even need the country populations data. You could just let the natural filtering take place by merging these tables into one and then just doing a simple sum on the cities population data

If this isn’t possible, another way you could potentially do it is again merge all these tables into one. By doing this your highest granularity will be your cities, which will have the population data on every single row. You might have the country population repeated on every single row for each country but then you could easily solve for that by either doing a min or a max calculation for that particular dimension that you want to count up.

If you did it this way then you wouldn’t really need to do any of this hard to work within the formula.

I do understand you said you couldn’t change the table structure, but it sounds weird to me, why not? Data modelling is a very important aspect of any Power BI analysis so you want to be able to edit your tables and merge tables like you most likely should be do doing here.

Thanks
Sam

3

@sripalavi, yes mate one fact table with the lowest “City” level is the optimum model I would have thought?

Then join this population fact table with a dimension table holding the City/State/Country combinations you require and you’re done for the matrix visual you wanted… :smiley:

I like the Inscope function though and it’s cool to use though…:+1:

1 Like
4

Hi Sam,
I actually thought of merging the tables but we are in such a stage where I am avoiding any regression and testing effort. Also, this dashboard is quite complex, has too many measures developed over last 6 months that i am not completely aware of.
But Thanks.

Regards
Pallavi Shree

5

In something like regional sales data, you’re right that you can just sum details to get higher levels. However, in cases like this, the data is actually different at hierarchy levels. For example, the population of a country does not equal the sum of the population of cities because lots of people don’t live in cities.