All language subtitles for 18. Defining Hierarchies in Power BI Desktop

Next up I want to touch upon one other option available in the data time power be-I which is the ability

to define hierarchies.

Now hierarchies are essentially groups of columns that you nest underneath each other to reflect multiple

levels of granularity or detail.

So for example a common type of hierarchy is a geographical one and that might include columns like

country followed by state followed by city and note that they become more granular more specific and

detailed with each level of the hierarchy.

So it's nice about these is that they can actually be treated as a single item in your tables and reports

so that users can view the data at the highest level and then drill up or drill down through the different

levels of detail in a meaningful way.

So one example here is creating a hierarchy with date fields.

So what you'd actually do in the data view is drill into your calendar table and you'd right click a

field.

In this case start of year and select a new hierarchy option from the menu.

Now what that does is it creates a brand new field that doesn't overwrite the existing ones creates

a brand new one that now contains.

Start of year and I'll show you what this looks like.

But it shows you a very clear icon that indicates this isn't just a regular field it's a hierarchy that

we're dealing with.

So then from there right click any other fields that you'd like to add.

Like start of month or start week or date for instance and instead of clicking new hierarchy you're

going to click add to hierarchy and select the date hierarchy or whatever you've named it that has already

been created.

So this is the method to actually build one of these hierarchies within the data view.

So one thing to note here is that I'm showing you a hierarchy example with Date field specifically but

Tarby is actually very very smart about dates.

And in fact it will create a lot of date related hierarchies for you automatically without having to

manually create a set like you see here.

But there are other examples where that's not the case or power be I won't automatically recognize fields

in a hierarchical way in which case building a set like this is very helpful.

So I to show you an example of that right now let's get back to our venture works report and I'll show

you what I mean.

All right so once you've opened up your venture works report file.

You should see the five table connections that we have in place.

Let's jump into that data view and for the sake of example here we're going to create a very simple

hierarchy using these territory fields.

And this is a good candidate to use here because we've got three different fields kind of in descending

order of detail or granularity continent is the broadest category followed by country followed by region.

So let's go ahead and start with Continent.

Right click Create new hierarchy as you can see it maintains or preserves that original continent field

and adds this new line item with this little icon that indicates that there are subfields within it.

In other words this is a hierarchy and it's named it by default consonant hierarchy since that was the

field that I had originally selected.

So you can right click and rename it.

I'm going to call it something like.

It's kind of in the way there something like territory hierarchy.

OK so it shifted things around since it's alphabetical order here and put that territory hierarchy there

at the bottom.

So the two other fields that I want to add to this are country and region.

So I can select country right click and add it to that territory hierarchy.

Same goes region.

Right click ADD.

Now it's just nested each of those fields under continent and created this hierarchy containing three

different levels.

If you added them in the incorrect order like continent region country you can simply click and drag

to reorder them around.

So not to recap once we get into the report view and start visualizing things building charts and graphs

instead of pulling in three individual fields for a continent country and region we could pull this

single hierarchy object into the visual and allow users to drill up or down to these different levels

of detail.

So a really interesting tool.

Great way to add more depth to power report.