Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 56 ยท Data Modelling

Model View, Hierarchies, Formatting and Display Folders

Tidy a Power BI model for report authors: diagram layout, hidden fields, display folders, hierarchies, number formats, sort by column and summarization defaults.

A model that calculates the right numbers can still be miserable to use. If the field list shows 140 columns, half of them technical keys, the months sort alphabetically and every number needs reformatting in every chart, report authors will lose time and trust. Model view and the column and measure properties are where you fix that once, for everyone.

This lesson is about the presentation layer of the model: how you arrange the diagram, what you hide, how you group measures, how you build drill paths, and how fields format and sort by default. It follows Date Tables and Time Intelligence Setup and comes right before we start writing DAX in DAX Basics.

Model view is a graphical part of Power BI Desktop, so it cannot run on this site. The steps below are described in text. The Python examples are stand-ins that reproduce what a property does (a number format, a sort-by column, a drill path) so you can see the effect.

The diagram in Model view

Click the third icon on the left edge of Power BI Desktop (the one that looks like three connected boxes) to open Model view. Every table appears as a card listing its fields, and relationships appear as lines between cards. You can drag cards, resize them and create layouts (tabs at the bottom) so one big model can be shown as several smaller diagrams, for example "Sales", "Inventory" and "Dates".

A good layout is more than decoration. It communicates the design:

  • Put fact tables in the middle and dimension tables around them, so the star shape from Star Schema is visible.
  • Arrange so that relationship lines flow from the one side (usually above) to the many side (below) without crossing.
  • Use one layout per business area on large models, and keep a small "everything" layout for orientation.

Layouts are saved in the file, but they have no effect on calculations. Moving a card changes nothing about the numbers.

Hide what report authors should not see

Many columns are plumbing: surrogate keys, foreign keys, helper columns used to sort or to build a relationship. Authors who drag CustomerKey into a chart get a meaningless bar chart. Select the field in Model view (or the Data pane) and toggle Hide in report view, or use the eye icon.

Hidden fields still work. Relationships, measures and sorting all keep using them, and you can still see them in Model view and Data view. They only vanish from the Fields pane in Report view. A reasonable rule: hide every key column and every column that is only used by a measure or a sort.

Two related tricks:

  • Hide a whole table if it only exists to feed a measure or a relationship (for example a disconnected helper table).
  • Hide the raw numeric column and expose a measure instead. If Sales[Amount] is hidden and Total Sales is visible, nobody can accidentally sum the wrong thing.

Display folders

When a model has 60 measures, a flat alphabetical list is hard to scan. A display folder is a text label stored in the field's properties. Select the measures, open the Properties pane, and type a name into Display folder. Fields with the same name appear grouped under a collapsible folder in the Fields pane.

Use a backslash to nest folders: Time Intelligence\Year to Date creates a folder named Time Intelligence containing a subfolder named Year to Date. Folders can hold both measures and columns, and the same folder name can appear in several tables.

Tips that scale well:

  • Group measures by purpose (Sales, Margin, Time Intelligence, Customers), not by the order you wrote them.
  • Keep measures on the table they relate to most, or create a dedicated empty table for them, and use folders either way.
  • Prefix helper measures with an underscore or put them in a folder named Helpers, and hide them.

Hierarchies

A hierarchy is an ordered list of columns that form a drill path, such as Year > Quarter > Month > Day or Category > Subcategory > Product. In the Data pane, right-click a column and choose Create hierarchy, then drag the other columns under it, or drag columns onto the hierarchy in Model view. The order inside the hierarchy is the drill order.

Why bother? Dropping one hierarchy into a chart axis gives the report reader the drill-down arrows for free, and authors do not have to remember which columns go together. A hierarchy is also the natural place to put levels you want in a matrix.

Rules of thumb for good hierarchies:

  • Build them on one table. Hierarchies cannot span tables, so a Category > Subcategory > Product path needs those columns in a single (denormalized) product dimension.
  • Each level should be a true parent of the next. Quarter 1 of 2025 and Quarter 1 of 2026 are different members, so a Quarter level needs the year in its label or sort key, or the drill path must pass through Year first.
  • Do not nest a column that has a one-to-one relationship with another, such as Month Name and Month Number, as two separate levels.

Note that the automatic date hierarchy Power BI adds to date columns (Auto date/time) is separate from this. With a proper date table you build your own hierarchy and can turn the automatic one off, as discussed in the date-table lesson.

Number formats and data categories

Formatting belongs to the column or measure, not to each chart. Select a field, open the Measure tools or Column tools ribbon, and set:

  • Format (Whole number, Decimal number, Currency, Percentage, Date, or a custom string).
  • Decimal places and thousands separator.
  • Data category (such as City, Country/Region, Web URL, Image URL, Latitude). The category tells maps where to plot and tells tables to show links or images.

Behind the buttons is a format string you can also type. Some useful ones:

Format string 1234.5 shows as Used for
#,0 1,235 whole numbers with separators
#,0.00 1,234.50 amounts
0.0% (value 0.153) 15.3% ratios
$#,0 $1,235 currency without cents
#,0,"K" 1K thousands, shown as K

Because the format is set once on a measure, every visual that uses it is consistent, and a card, a table and a tooltip never disagree about decimals. For a measure that returns a ratio, store the true ratio (0.153) and format it as a percentage, so later calculations still work.

Python Format strings on a few values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

This is a stand-in: Power BI uses its own format-string language, not Python's. The point to take away is that formatting changes the display, never the stored value.

Sort by column

By default a text column sorts alphabetically, so Apr, Aug, Dec, Feb .... Months, weekdays and custom categories such as Low, Medium, High need a different order. The fix is Sort by column: select the text column (say Month Name), open Column tools, choose Sort by column, and pick a numeric column such as Month Number.

Requirements and gotchas:

  • Each value of the text column must map to exactly one sort value. If January has sort values 1 and 13, Power BI raises an error.
  • The sort column is usually hidden, since report authors never need it.
  • Sorting a column by another column affects every visual that uses the column, which is exactly what you want.
Python Alphabetical vs sort by column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second half of that example is the drill path: expanding one level at a time. Notice that Q1 appears under both years as separate members. That is why a Quarter level only makes sense below Year.

Summarization defaults

A numeric column dragged into a chart is summed by default (you see a ฮฃ sign next to it). That is not always right: summing a Year column or a UnitPrice column is meaningless. Select the column and, on Column tools, change Summarization to Don't summarize, Average, Min, Max, Count and so on.

Rules that prevent embarrassing charts:

  • Set Don't summarize on years, IDs, postal codes and ranks, even when they are numbers. Better, set their data type to text where appropriate.
  • Set Average (or better, write a measure) for prices and rates.
  • Prefer explicit measures for anything that matters, and hide the raw column. A measure defines the calculation once instead of relying on the author's choice of aggregation.

Synonyms and descriptions

Two small properties help the people using your model:

  • Description: a sentence that appears when someone hovers over the field in the Fields pane. Write what the measure means in business words ("Sales after discounts, excluding tax").
  • Synonyms: alternative names used by Q&A and Copilot. If your column is CustAcctNo, add synonyms like customer number so natural-language questions find it.

Good field names matter more than any property. Rename cust_ac_no to Customer Number in Power Query, so the Fields pane reads like English.

A quick pre-release checklist

Before you publish a model, open Model view and walk through it:

Tidy the model

Hide plumbing Keys and sort columns.
Group measures Display folders.
Add hierarchies Drill paths on one table.
Set formats Once, on the field.
1 / 2

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A relationship uses CustomerKey on both tables. What should you do with the CustomerKey column in the report-facing field list?

    Hidden fields still take part in relationships, sorting and measures. Hiding only removes them from the Fields pane in Report view.
  2. 2. How do you make Month Name sort January to December instead of alphabetically?

    Sort by column tells Power BI to order the text column by a numeric column. Each month name must map to exactly one number.
  3. 3. Which statement about hierarchies is true?

    Hierarchies live on one table. If you want Category > Subcategory > Product, those columns must be in the same table.
  4. 4. Why set the format on the measure instead of on each visual?

    A measure-level format is the default for every chart, card and tooltip. It changes only the display, never the stored value, and has no effect on speed.
  5. 5. Which is the best summarization setting for a Year column stored as a whole number?

    Adding years together is meaningless. Don't summarize makes Power BI treat the column as a category, as an axis would.

Summary

  • Model view arranges the diagram into layouts; a clear star-shaped layout documents your design and changes no numbers.
  • Hide keys, helper columns and raw columns that a measure replaces; hidden fields still work.
  • Display folders (with \ for nesting) group measures; hierarchies create drill paths on a single table.
  • Set formats, data categories, sort by column and summarization once on the field.
  • Descriptions and synonyms make fields self-explanatory for people and for Q&A.

Next, we start writing calculations in DAX Basics: Syntax, Columns and Measures.