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