Multi-level or hierarchical data

Power BI Data Model
1

I have a scenario I’m trying to set up and I just wondered if you had any tutorials that you could direct me to that might cover off something similar?

I’ll explain my situation below, but essentially it is a pretty common scenario, where you might need to report on data at multiple levels, such as:

  • team member / team / division, or

  • city / country / continent

  • student / course / degree

  • etc, etc

My situation relates to NZ population data …

The NZ Dept of Statistics breaks the country down into “meshblocks”, which are a very low level of granularity.

Meshblocks can then be combined in a number of ways.

In my example, meshblocks are grouped to form “Community Boards”.

In turn, Community Boards can be combined to form “Territorial Authorities” (aka “Councils”).

I have a fact table that contains some population data down at the “meshblock” level and I need to produce some reports from it, based on Councils and Community Boards.

I just want to be sure that I’m setting up my data model in the best way, that allows my reports to be filtered easily down to a specific council level, or lower still, to a particular community board.

I’m guessing you’ve probably covered off something similar in one of your many excellent tutorials, so if you are able to point me in the right direction, that would be much appreciated.

Thanks for your help!

2

Hi Graig,

Here’s a video on Hierarchies in Power BI

And a link to a post on creating Hierarchies across tables

3

@CraigMac

There are a number of issues with these kind of queries which relate to the use of ragged hierarchies which power bi doesn’t handle well.

I think Sam touches on these kind of formatting problems in his financial statements examples and I have found the use of the new DAX Inscope function can help the matrix table work when dealing with hierarchy totals.

There is a useful visual that allows you to remove blank leafs in a hierarchy.

Capture
Capture.PNG717×401 19.1 KB

1 Like
4

Thanks Melissa

5

Thanks Garry.

6

@CraigMac no probs don’t forget the setting to turn off the Blank leafs…its the reason why this visual works for this purpose. :smile: Let us know how you go!