Nick's Power BI Tricks - Data Modeling Edition

In this edition of Nick’s Power BI Tricks, Nick Lee shares practical tips and tricks to streamline your Power BI data modeling experience. Rather than a full modeling tutorial, this session focuses on productivity hacks that help users manage complex models more efficiently.

Creating a Measures Repository

Nick introduces the concept of a measures repository—a dedicated table to store all your DAX measures in one place:

1. Use the Enter Data feature to create a new table named _Measures.
2. Add a dummy column (e.g., HideMe) with a placeholder value.
3. Load the table and move existing measures into it using the Model View.
4. Hide the dummy column to convert the table into a measures-only table (Power BI will display a calculator icon).

Organizing Measures with Display Folders

To manage large numbers of measures, Nick recommends using display folders:

• In Model View, select a measure and use the Display Folder field in the Properties pane.
• Type in a folder name (e.g., “Sales Metrics”) to group related measures.
• Repeat for other measures to create a clean, organized structure.

Adjusting Aggregation Behavior

Nick demonstrates how to fix inappropriate default aggregations:

• For example, Calendar Year might default to Sum, which is incorrect.
• In Model View, select the column and change the Summarize By option to None.
• This removes the auto-aggregation (Sigma icon) and ensures correct behavior in visuals.

Managing Implicit Measures

Nick also shows how to control implicit measures:

• Change the default aggregation (e.g., from Sum to Min) for numeric fields.
• This ensures that visuals reflect the intended logic without needing explicit DAX measures.

Simplifying Model View with Layout Tabs

To handle large models, Nick suggests using custom layout tabs:

1. Create a new layout tab in Model View.
2. Drag in a fact table (e.g., FactSalesQuota).
3. Right-click and choose Add Related Tables to auto-populate connected dimensions.
4. Use Collapse All to simplify the view and focus on specific model sections.

Highlighting Relationships

Nick highlights a useful checkbox: “Show related fields when card is collapsed”. This helps users quickly identify relationships between tables even when fields are hidden or minimized.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.