What is Power BI Hierarchy?
In Power BI, a hierarchy is a logical organizational structure used to represent data with various levels of granularity, typically in a tree-like format. Hierarchies are commonly used for dimensions like dates, geographical locations, or organizational structures. They allow users to drill down or roll up through data for better analysis and reporting. Power BI enables you to create and use hierarchies to make your data more accessible and insightful.
Table of contents
Key Takeaways
- Power BI hierarchies organize data into levels for more granular or high-level analysis.
- Creating hierarchies in Power BI is flexible and can be done manually or automatically.
- Hierarchies are valuable for exploring data and creating interactive reports that allow users to drill down or roll up through data as needed.
- You can create Power BI Hierarchy Slicer, Tree, Visual, and charts using standard visual templates.
How to create a Power BI Hierarchy?
To create a hierarchy in Power BI, follow these steps:
Step 1: Open Power BI Desktop and load your data source.
Step 2: In the “Fields” pane, select the data field you want to create a hierarchy from.
Step 3: Right-click on the field and choose the Create Hierarchy option.
Step 4: This will create a Power BI hierarchy in the Fields pane.
Step 5: Right-click on the hierarchy and choose Rename option.
Step 6: Right-click on the fields you want to add to the hierarchy, choose Add to hierarchy, and then the hierarchy name (For example: Sales Hierarchy)
Step 7: You can also rearrange the fields in the hierarchy by navigating to the Model view, choosing the Power BI hierarchy, and performing the Drag and drop of columns in the properties blade as per the level you want to change. Once the rearrangements are done, click on Apply Level Changes.
Step 8: You can also nest hierarchies within each other by creating sub-hierarchies within the main hierarchy.
You can create a Power BI Hierarchy Slicer to filter data based on Hierarchical attributes. Similarly, you can also create Tree for visually representing the Hierarchial dataset. For data visualization, you can create a Visual and use charts using standard visuals.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to use Power BI Hierarchy?
To use the Power BI Hierarchy, follow the steps highlighted below:
Step 1: In your report, add a Power BI visual (e.g., a chart or table) that includes the hierarchy field.
Step 2: Click on the hierarchy field within the visual.
Step 3: Power BI will display an icon that allows you to expand or collapse the hierarchy levels.
Step 4: Click the icon to drill down or roll up through the Power BI hierarchy levels to view data at different levels of granularity.
Examples
In this section, we will look at some of the examples of creating relationships in Power BI.
Example #1 – Creating the Year-Quarter-Month Hierarchy to View
In this example, we will create the Year-Quarter-Month Hierarchy in Power BI using the Significant Earthquakes dataset. The Significant Earthquakes dataset contains
- database – This dataset includes a record of the date, time, location, depth, magnitude, and source of every earthquake with a reported magnitude of 5.5 or higher since 1965.
To create a Year-Quarter-Month hierarchy, follow the steps below:
Step 1: Open Power BI Desktop, import the Significant Earthquakes dataset using the Get data option, and then click on the Load button.
Step 2: Navigate to the Fields pane, select the fields for which you want to create the hierarchy, right-click on the field, and select the New column option.
Step 3: Enter the DAX in the formula bar to create the new column
Here, we have created multiple new columns using the Ship Date field.
Year = YEAR(database[Date])
Quarter = QUARTER(database[Date])
Month = MONTH(database[Date])
Click on the Commit icon to save the changes.
Step 4: Right-click on the field for which you want to create the hierarchy and choose the Create hierarchy option.
It will create a new hierarchy in the Fields pane.
Step 5: Right-click on the newly created columns and add them to the new hierarchy.
Step 6: Once all the columns are added to the new hierarchy, you can view them under the new hierarchy.
Step 7: Navigate to the Visualizations pane, select the Clustered column chart visual icon, and drag and drop the fields from the Fields pane to the X-axis and Y-axis, respectively.
Here, we have mapped the fields Year in the Count(ID) to the X-axis.
It will create a column chart in the report canvas, as shown below.
Example #2 – Creating date hierarchy automatically on Power BI
In this example, we will create the Year-Quarter-Month Hierarchy in Power BI using the AdidasSalesdata dataset. The AdidasSalesdata dataset contains
- Sales_details – Contains information about the various retailers, demographic details, and sales details across the Adidas Stores.
To create a date hierarchy, follow the steps below:
Step 1: Open Power BI Desktop and import the AdidasSalesdata dataset using the Get data option.
Step 2: Select the Sales_details dataset available in the AdidasSalesdata file and then click on the Load button.
Step 3: Navigate to the Fields pane and expand the column to view the Year-Quarter-Month hierarchy.
As you can see, Power BI has auto-created the Date Hierarchy on the Invoice Date field.
Example #3 – Creating date hierarchy from the date on Power BI
In this example, we will create a date hierarchy from the date on Power BI using the Country_Product_Sales dataset. Country_Product_Sales dataset contains
The steps to create the date hierarchy are outlined below:
Step 1: Open Power BI Desktop and import the Country_Product_Sales dataset using the Get data option and then click on the Load button.
Step 2: Navigate to the Fields pane and expand the Date field. You will notice that Power BI has automatically created the date hierarchy for the Date field.
Step 3: Right-click on the Date field and choose the New column option.
Step 4: Enter the DAX in the formula bar to create the new column.
Here we have created 3 new columns i.e. Day, Month, and Quarter.
Day = DAY(financials[Date])
Month = MONTH(financials[Date])
Quarter = QUARTER(financials[Date])
Once you have completed with DAX, click on the Commit icon to save the changes.
Step 5: Right-click on the Date field and choose the Create hierarchy option.
This will create a Date Hierarchy in Power BI for the financials dataset.
Step 6: Right-click on each of the newly created columns, select Add to hierarchy, and then the hierarchy name i.e. Date Hierarchy. Here we have repeated this step for the Day, Month, and Quarter columns to add them to the Date Hierarchy.
Step 7: Once the columns have been added to the Date Hierarchy, you will be able to view them in the Fields pane.
Example #4 – Creating a custom date hierarchy
In this example, we will create a custom date hierarchy in Power BI using the Bakery_Sales dataset. The Bakery_Sales dataset provides the transaction details of customers who ordered different items from this bakery online.
To create the custom date hierarchy, follow the steps highlighted below:
Step 1: Open Power BI Desktop, import the Bakery_Sales dataset using Get data, and then load it into Power BI.
You will notice that Power BI has created a date hierarchy automatically for the date field.
Step 2: Right-click on the Datetime field and select the New column option.
Step 3: Enter the DAX expressions in the formula bar to create the new columns.
Here we have created the below custom date columns.
DaysOfWeek = FORMAT(Bakery_Sales[DateTime],”dddd”)
MonthName = FORMAT(Bakery_Sales[DateTime],”MMM”)
YearMonthNumber = FORMAT(Bakery_Sales[DateTime], “YYYY/MM”)
YearQuarter = FORMAT(Bakery_Sales[DateTime], “YYYY/Q”)
Step 4: Right-click on the field you want to create a hierarchy for and choose the Create hierarchy option.
This will create an auto hierarchy in the Fields pane.
Step 5: Right-click on the newly created columns, select Add to hierarchy, and then DateTime Hierarchy.
You will see all the columns added to the DateTime Hierarchy as shown below.
Example #5 – Creating date hierarchy with a week on Power BI
In this example, we will create a date hierarchy with a week on Power BI using the Customer_Transactions dataset. The Customer_Transactions dataset contains a Synthetic Dataset of Customer Transactions with Demographic and Shopping Behavior Information.
The steps to create a date hierarchy are highlighted below:
Step 1: Open Power BI Desktop and import the Customer_Transactions dataset using the Get data option and then click on the Load button to load data into the Power BI Desktop.
Step 2: Navigate to the Fields pane and expand the Date field to see the auto-created date hierarchy by Power BI.
Step 3: Right-click on the Date field and choose the New column option.
Step 4: Enter the DAX in the formula bar to create the new column.
Here, we have created three new columns, i.e., Day, Month, and Quarter.
DAY = DAY(Customer_Transactions[Date])
WEEK = WEEKNUM(Customer_Transactions[Date])
MONTH = MONTH(Customer_Transactions[Date])
QUARTER = QUARTER(Customer_Transactions[Date])
YEAR = YEAR(Customer_Transactions[Date])
Once you have completed with DAX, click on the Commit icon to save the changes.
Step 5: Right-click on the Date field and choose the Create hierarchy option.
It will create a Date Hierarchy in Power BI for the Customer_Transactions dataset.
Step 6: Right-click on each of the newly created columns, select Add to hierarchy, and then the hierarchy name, i.e., Date Hierarchy. Here, we have repeated this step for the Day, Month, and Quarter columns to add them to the Date Hierarchy.
Step 7: Once the columns have been added to the Date Hierarchy, you will be able to view them in the Fields pane.
Important Things to Note
- Hierarchies can improve data analysis and make it easier to navigate large datasets.
- You can create hierarchies for dimensions other than dates, such as product categories or geographic regions.
- Hierarchies can be used in various visualizations like charts, tables, and slicers.
Frequently Asked Questions (FAQs)
To remove the date hierarchy in Power BI, follow the steps highlighted below.
Step 1: Navigate to the Fields pane in Power BI
Step 2: Choose the date hierarchy you want to remove
Step 3: Right-click on the date hierarchy and choose Delete from model option.
Step 4: Confirm the delete hierarchy by choosing the Yes button.
To change hierarchy levels in Power BI, follow the steps below.
Step 1: Navigate to the Model view in Power BI.
Step 2: Select the hierarchy for which you want to change the hierarchy levels.
Step 3: Drag and drop the columns in the properties blade as per the level you want to change.
Once the changes are made, click on Apply Level Changes.
Step 4: Now, you will be able to view the changes to the hierarchy in the Fields pane, as shown below.
To rename the hierarchy in Power BI, follow the steps highlighted below:
Step 1: Navigate to the Fields pane in Power BI.
Step 2: Choose the hierarchy you want to rename.
Step 3: Right-click on the hierarchy and choose Rename option.
Step 4: Provide an appropriate name to the hierarchy and hit the Enter key. You will see the renamed hierarchy in the Fields pane, as highlighted below.
Recommended Articles
This has been a guide to Power BI Hierarchy. Here we learn the steps to create power bi Hierarchy, how to use, with examples and points to remember. You can learn more from the following articles –
Leave a Reply