Active vs inactive relationships in Power BI - Dashboard Approaches Compared

Dashboard Mode - Active vs inactive relationships

Business Question

How do active and inactive relationships affect sales analysis by product category and promotion period?

Sample Data

SaleID	ProductID	SaleDate	Quantity	Amount
1	101	2024-01-05	2	200
2	102	2024-01-15	1	150
3	103	2024-02-10	3	450
4	101	2024-02-20	1	100
5	102	2024-03-05	4	600

ProductID	Category
101	Electronics
102	Furniture
103	Electronics

PromoID	StartDate	EndDate
1	2024-01-01	2024-01-31
2	2024-02-01	2024-02-28

Dashboard Components

• KPI Card: Total Sales Amount
  Formula: Total Sales = SUM(Sales[Amount])
  Result: 1500
• Bar Chart: Sales by Product Category
  Formula: Sales by Category = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[SaleDate], Promotion[StartDate]))
  Explanation: Uses inactive relationship between Sales[SaleDate] and Promotion[StartDate] to show sales during promotion periods.
• Table: Sales Details with Active Relationship
  Shows sales grouped by Product Category using the active relationship between Sales and Product tables on ProductID.

Dashboard Layout

+----------------------+-------------------------+
|      Total Sales      |  Sales by Product Category |
|       (KPI Card)      |       (Bar Chart)         |
+----------------------+-------------------------+
|               Sales Details Table               |
+-------------------------------------------------+

Interactivity

Filters on Promotion Period update the Bar Chart using the inactive relationship activated by USERELATIONSHIP. The KPI Card and Sales Details Table use the active relationships and update based on Product Category or date filters.

Self Check

Add a filter for Promotion Period = January 2024. Which components update their data?

• The Bar Chart updates to show sales only in January 2024 using the inactive relationship.
• The KPI Card and Sales Details Table do not filter by Promotion Period because they use the active relationship on ProductID.

Key Result

Dashboard showing total sales, sales by product category using inactive relationship for promotion periods, and detailed sales with active relationships.