Data Modeling, DAX, Visualization0%
Publishing and Sharing Reports

Data Modeling, DAX, Visualization

Beginner12 min read•Updated: Jul 11, 2026
Study MaterialsCheatsheet

Capstone Project: Model Relationships, DAX Measures & Visualization

Following our Power Query ETL transformations, we now configure the physical relationships in Model View, build out the centralized KPI measure library, and design the multi-page user interface.

1. Configuring Model Relationships

In Power BI Model View, establish the following relationships:

Output
[Dim_Customer]
CustomerID (1)
|
| (1 : *) Single
v
[Dim_Product] ---> [Fact_SalesOrders] <--- [Dim_Geography]
SubCat (1 : *) ^ ^ City (1 : *)
| (Active) | (Inactive)
| |
[Dim_Date] (ShipDate via USERELATIONSHIP)
Date (1)
  1. 1
    Dim_Customer[CustomerID] ($1$) $\to$ Fact_SalesOrders[CustomerID] ($*$)
  2. 2
    Dim_Product[SubCategory] ($1$) $\to$ Fact_SalesOrders[SubCategory] ($*$)
  3. 3
    Dim_Geography[City] ($1$) $\to$ Fact_SalesOrders[City] ($*$)
  4. 4
    Dim_Date[Date] ($1$) $\to$ Fact_SalesOrders[OrderDate] ($*$) [ACTIVE]
  5. 5
    Dim_Date[Date] ($1$) $\to$ Fact_SalesOrders[ShipDate] ($*$) [INACTIVE]

2. Core Capstone DAX Measure Library

Create a dedicated empty table named _Key Measures:

DAX
-- 1. Base Net Revenue
Total Sales = SUM(Fact_SalesOrders[Sales])
 
-- 2. Base Net Profit
Total Profit = SUM(Fact_SalesOrders[Profit])
 
-- 3. Profit Margin %
Profit Margin % =
DIVIDE(
[Total Profit],
[Total Sales],
0
)
 
-- 4. Cumulative Year-to-Date Sales
Sales YTD =
TOTALYTD(
[Total Sales],
Dim_Date[Date]
)
 
-- 5. Prior Year Benchmark & YoY Growth
Sales Prior Year =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(Dim_Date[Date])
)
 
Sales YoY Growth % =
DIVIDE(
[Total Sales] - [Sales Prior Year],
[Sales Prior Year],
0
)
 
-- 6. Sales by Shipping Date (Inactive Relationship)
Sales by Ship Date =
CALCULATE(
[Total Sales],
USERELATIONSHIP(Fact_SalesOrders[ShipDate], Dim_Date[Date])
)

3. Designing the Final Capstone Dashboard

Build three interconnected pages:

  1. 1
    Executive Overview Page: KPI Cards (Sales, Profit, Margin %, YoY Growth), Monthly Trend Combo Chart, Category Breakdown.
  2. 2
    Regional Deep-Dive Page: Map visual, City-level matrix table, Region drillthrough.
  3. 3
    Product Detail Drillthrough Page: Customer list, order history, and discount sensitivity analysis.
Interactive Quiz

Multiple Choice Questions

Question 1
In which view in Power BI Desktop do you verify that relationship cardinality is set to One-to-Many ($1 : *$) with Single cross-filter direction?
Question 2
How do you create an empty table dedicated exclusively to storing DAX measures in Power BI Desktop?
Question 3
What does the measure Sales by Ship Date calculate when it utilizes USERELATIONSHIP(Fact_SalesOrders[ShipDate], Dim_Date[Date])?
Question 4
What color and styling best practices should be applied to financial and sales executive dashboards?
Question 5
Why should every business measure be formatted (e.g., Currency $#,##0, Percentage 0.0%) immediately upon creation?
Next Lesson

Publishing and Sharing Reports

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
End-to-End Project with Real DatasetPublishing and Sharing Reports

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext