Data model best practices in Power BI - Dashboard Guide

Dashboard Mode - Data model best practices

Goal

Understand how to build a clean and efficient data model in Power BI to create fast and accurate reports.

Sample Data

OrderID	CustomerID	ProductID	OrderDate	Quantity	Price	Region
1001	C001	P001	2024-01-10	2	50	North
1002	C002	P002	2024-01-15	1	30	South
1003	C001	P003	2024-02-05	5	20	North
1004	C003	P001	2024-02-20	3	50	East
1005	C004	P002	2024-03-01	4	30	West
1006	C002	P003	2024-03-15	2	20	South

CustomerID	CustomerName	Segment
C001	Alice	Retail
C002	Bob	Wholesale
C003	Charlie	Retail
C004	Diana	Wholesale

ProductID	ProductName	Category
P001	Widget	Gadgets
P002	Gizmo	Gadgets
P003	Thingamajig	Tools

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])
  Shows total revenue from all sales.
• KPI Card: Total Orders
  Formula: Total Orders = DISTINCTCOUNT(Sales[OrderID])
  Shows total number of orders.
• Bar Chart: Sales by Region
  X-axis: Region
  Y-axis: Total Sales (using Total Sales measure)
  Shows sales distribution across regions.
• Table: Sales Details
  Columns: OrderID, CustomerName, ProductName, Quantity, Price, Total Sale (calculated as Quantity * Price)
  Shows detailed sales data combining related tables.
• Relationships
  Sales[CustomerID] linked to Customer[CustomerID]
  Sales[ProductID] linked to Product[ProductID]
  These relationships enable combining data from multiple tables efficiently.

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |     Total Orders     |
|      (KPI Card)       |      (KPI Card)      |
+----------------------+----------------------+
|                      Sales by Region Chart                      |
|                          (Bar Chart)                           |
+---------------------------------------------------------------+
|                         Sales Details Table                    |
+---------------------------------------------------------------+

Interactivity

Filters on Region and Category allow users to narrow down data. Selecting a region updates the bar chart, KPI cards, and sales details table to show only data for that region. Selecting a product category filters the sales details and charts accordingly. Relationships ensure filters flow correctly between tables.

Self Check

If you add a filter for Region = South, which components update and what changes?

• Total Sales and Total Orders KPI cards show totals only for South region.
• Sales by Region bar chart highlights or filters to South region data.
• Sales Details Table shows only orders from South region.

Key Result

A Power BI dashboard demonstrating best practices in data modeling with clean relationships, KPIs, charts, and detailed tables.

Practice

1. What is the main reason to use a star schema in Power BI data modeling?

easy

A. It hides data from users to improve security.

B. It allows storing all data in one big table.

C. It automatically creates relationships without keys.

D. It simplifies analysis by separating facts and dimensions clearly.

Solution

1. Step 1: Understand star schema purpose

   A star schema organizes data into fact tables (measures) and dimension tables (descriptions) for clarity.

2. Step 2: Recognize benefits in Power BI

   This separation makes analysis easier and faster by simplifying relationships and queries.

3. Final Answer:

   It simplifies analysis by separating facts and dimensions clearly. -> Option D

4. Quick Check:

   Star schema = clear fact and dimension tables [OK]

Hint: Star schema means clear fact and dimension tables [OK]

Common Mistakes:

• Thinking all data should be in one table
• Believing relationships form without keys
• Confusing star schema with security features

2. Which of the following is the correct way to create a one-to-many relationship in Power BI?

easy

A. Link the primary key in the dimension table to the foreign key in the fact table.

B. Link the foreign key in the dimension table to the primary key in the fact table.

C. Create a many-to-many relationship between two fact tables.

D. Use a calculated column to join unrelated tables.

Solution

1. Step 1: Identify keys in tables

   The dimension table has a primary key; the fact table has a foreign key referencing it.

2. Step 2: Create relationship direction

   One-to-many means one dimension row links to many fact rows, so connect primary key (dimension) to foreign key (fact).

3. Final Answer:

   Link the primary key in the dimension table to the foreign key in the fact table. -> Option A

4. Quick Check:

   One-to-many = primary key to foreign key [OK]

Hint: Primary key (dimension) links to foreign key (fact) [OK]

Common Mistakes:

• Reversing keys in relationship
• Using many-to-many where one-to-many is needed
• Trying to join unrelated tables with calculated columns

3. Given a data model with a Sales fact table and a Product dimension table linked by ProductID, what happens if you add a relationship from Sales[ProductID] to Product[ProductID] but the Product table has duplicate ProductIDs?

medium

A. Power BI will create a one-to-many relationship and filter correctly.

B. Power BI will show an error and not allow the relationship.

C. Power BI will create a many-to-many relationship automatically.

D. Power BI will ignore duplicates and create the relationship anyway.

Solution

1. Step 1: Understand relationship requirements

   One-to-many relationships require the 'one' side (Product) to have unique keys.

2. Step 2: Check effect of duplicates

   Duplicates in Product[ProductID] break uniqueness, so Power BI blocks the relationship creation.

3. Final Answer:

   Power BI will show an error and not allow the relationship. -> Option B

4. Quick Check:

   Unique keys required on 'one' side [OK]

Hint: One side keys must be unique to create relationship [OK]

Common Mistakes:

• Assuming duplicates are ignored
• Expecting automatic many-to-many creation
• Thinking relationship will still filter correctly

4. You created a relationship between Customer[CustomerID] and Sales[CustomerID], but your report shows incorrect totals. Which of the following is the most likely cause?

medium

A. The Sales table is missing CustomerID values.

B. CustomerID has duplicates in the Customer table.

C. The relationship is inactive and needs to be activated.

D. The relationship is many-to-many instead of one-to-many.

Solution

1. Step 1: Check relationship status

   Inactive relationships do not filter data automatically, causing wrong totals.

2. Step 2: Confirm activation needed

   Activating the relationship or using DAX USERELATIONSHIP fixes filtering issues.

3. Final Answer:

   The relationship is inactive and needs to be activated. -> Option C

4. Quick Check:

   Inactive relationship causes wrong totals [OK]

Hint: Inactive relationships block filtering; activate them [OK]

Common Mistakes:

• Ignoring inactive relationship status
• Assuming duplicates always cause wrong totals
• Thinking missing values break relationships

5. You have a complex data model with multiple fact tables and dimension tables. To improve performance and clarity, which best practice should you apply?

hard

A. Use a star schema with clear one-to-many relationships and avoid circular dependencies.

B. Combine all tables into one large table to reduce relationships.

C. Create many-to-many relationships between all tables for flexibility.

D. Use calculated columns to join tables instead of relationships.

Solution

1. Step 1: Identify best modeling approach

   Star schema organizes data clearly with fact and dimension tables linked by one-to-many relationships.

2. Step 2: Avoid common pitfalls

   Combining tables or many-to-many relationships can cause confusion and slow performance; calculated columns for joins are inefficient.

3. Final Answer:

   Use a star schema with clear one-to-many relationships and avoid circular dependencies. -> Option A

4. Quick Check:

   Star schema + one-to-many + no circular dependencies [OK]

Hint: Star schema and one-to-many improve clarity and speed [OK]

Common Mistakes:

• Merging all tables into one
• Using many-to-many everywhere
• Relying on calculated columns for joins