In order to use create hierarchy field with your Calendar Table, you will first have to extract the necessary date component. If you have no idea what\u2019 I\u2019m referring to, I\u2019ve talk about in the\u00a0previous part\u00a0of Calendar Table with DAX articles.<\/p>\n\n\n\n
If you are dealing with many dimension table (AKA database table) with many fields in it, in a way hierarchy can help makes it easier for you to manage those fields into one. I guess you know how crazy it can be scrolling up and down searching for that field while building report using Pivot Table of creating visualization while using Power View.<\/p>\n\n\n\n
Hierarchy in a way is about group the relevant fields into one field. Example if you have geographic data or date data, you can add the hierarchy that had the year at the top, follow by quarter then month (Example: Year [2010] <\/em>\u00e0 Quarter [Q1] <\/em>\u00e0 Month [January]).<\/em> Meanwhile, for the geographic data you can add the region at the top, then follow by country then states (Example: Region [Asia]<\/em>\u00e0 Country [Malaysia]<\/em>\u00e0 State [Kuala Lumpur]<\/em>). Creating the hierarchy can help you simplified the view and the number of fields being displayed in the Pivot Table field list, and also you can create the coolest type of drill down\/up report. Don\u2019t understand how drilling up or down works? You gotta continue reading to see how cool your report is turning out to be.<\/p>\n\n\n\n To work with hierarchy alone is not going to be enough. I will also walk you through how to hide some of the column from the client view (you know those fields where you will not be needing while creating report, usually are helper columns), so you are not drowning yourself in those fields from the tables in Data Model.<\/p>\n\n\n\n Since we\u2019re still in the middle of talking about date dimension table, so the example is definitely date related.<\/p>\n\n\n\n After extracting all the date component (If you have been following me since Part 1<\/em>), you should have a calendar as shown below:<\/p>\n\n\n\n To create the hierarchy, you will have to change your Data Model window\u2019s view to Diagram View.<\/p>\n\n\n\n This is how the diagram view of your calendar table looks like.<\/p>\n\n\n\n Right click at the field that will be sitting at the most top and select \u201cCreate Hierarchy\u201d<\/p>\n\n\n\n Create a name for the hierarchy<\/p>\n\n\n\n Gradually add all the field that to the \u201cPeriod\u201d hierarchy. You can do it by drag and drop or right click and select from the existing hierarchy.<\/p>\n\n\n\n After you have finish adding the fields to the hierarchy, this is how is should look like.<\/p>\n\n\n\n Now close the Data Model window, back to the Excel sheet and insert a pivot table. check out the \u201cPeriod\u201d field from the Pivot Table Field list.<\/p>\n\n\n\n The rest of the fields that are not being grouped in the hierarchy can be found in the \u201cMore fields\u201d section. If you were to expand the \u201cMore Fields\u201d section, you should be able to find all the fields from Date table.<\/p>\n\n\n\n If you would like to change the position of those fields in the hierarchy field, you can simple drag and drop the position, or once you right click the field select move up or down to reposition those fields.<\/p>\n\n\n\n You can also rename the hierarchy field name by right clicking the Hierarchy and \u201cRename\u201d.<\/p>\n\n\n\n This is the most awesome part after creating the hierarchy. It makes the process of creating report so much easier. Let\u2019s say, I\u2019m going to create a report showing my sales over the starting of the transaction until the last date of the transaction.<\/p>\n\n\n\n Simply drag the \u201cPeriod\u201d field into the ROWS\/ COLUMNS in the Pivot Table builder area. You should be able to get the result as shown in the figure below:<\/p>\n\n\n\n Notice while you are selecting the field in the Pivot Table, there is a drill down command being enabled in the Analyze Tab? That the awesome part of it!!!!<\/p>\n\n\n\n After selecting the \u201cDrill Down\u201d button, the whole report now showing the Sales by Quarter. If just now your cursor was selecting at the FY2011 and the drill down report will be the report based on FY2011. For my version of report, it is now showing the report of the Sales by Financial Quarter by FY2011.<\/p>\n\n\n\n After drilling it down, you can also select \u201cDrill Up\u201d to go back up.<\/p>\n\n\n\n Alright, come to the last part of clearing up the client view. Client view which mean the area where you build reports, Pivot Table field pane. Back to the Data Model window, you can do this either in the Data View or Diagram View.<\/p>\n\n\n\n The reason why we created the hierarchy field in the first pace is to group the necessary field in one, which also means the other fields are not going to be needed in report creation. Hence, to clearing up the view while building report, I will usually hide them.<\/p>\n\n\n\n Right click the field you wish to hide and select \u201cHide from Client Tools\u201d.<\/p>\n\n\n\n After hiding them fields, you should see the column turns semi-transparent. This means you have successfully hid them from the Client View.<\/p>\n\n\n\n To unhide them, simply right click and select \u201cUnhide from Client Tools\u201d<\/p>\n","protected":false},"excerpt":{"rendered":" Hierarchy in PowerPivot with Calendar table In order to use create hierarchy field with your Calendar Table, you will first have to extract the necessary date component. If you have no idea what\u2019 I\u2019m referring to, I\u2019ve talk about in the\u00a0previous part\u00a0of Calendar Table with DAX articles. If you are dealing with many dimension table […]<\/p>\n","protected":false},"author":4,"featured_media":897,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-917","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/917","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=917"}],"version-history":[{"count":2,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/917\/revisions"}],"predecessor-version":[{"id":1850,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/917\/revisions\/1850"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/897"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=917"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=917"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=917"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Creating Date field hierarchy<\/h4>\n\n\n\n
Editing the Hierarchy<\/h4>\n\n\n\n
Renaming and deleting Hierarchy<\/h4>\n\n\n\n
Create Pivot Report with Hierarchy Field<\/h4>\n\n\n\n
Drill Down report<\/h4>\n\n\n\n
Hide fields from Client Tools<\/h4>\n\n\n\n