Relationship direction and cross-filtering in Power BI - Dashboard Guide

Dashboard Mode - Relationship direction and cross-filtering

Business Question

How does changing the relationship direction and cross-filtering between Sales and Products tables affect the total sales and product details shown in the dashboard?

Sample Data

SalesID	ProductID	Quantity	SalesAmount
1	101	2	200
2	102	1	150
3	103	3	450
4	101	1	100
5	104	5	500

ProductID	ProductName	Category
101	Notebook	Stationery
102	Pen	Stationery
103	Chair	Furniture
104	Desk	Furniture
105	Lamp	Furniture

Dashboard Components

• KPI Card: Total Sales Amount
  Formula: Total Sales = SUM(Sales[SalesAmount])
  Result: 1400
• Table Visual: Product Sales Details
  Columns: ProductName, Category, Total Quantity Sold, Total Sales Amount
  Measures:
  Total Quantity Sold = CALCULATE(SUM(Sales[Quantity]))
Total Sales Amount = CALCULATE(SUM(Sales[SalesAmount]))
  Shows sales grouped by product
• Slicer: Category Filter
  Based on Products[Category]

Relationship Setup: Single direction from Products to Sales on ProductID by default.

Cross-filtering: Changing relationship direction to both directions allows filtering from Sales to Products and vice versa.

Dashboard Layout

+----------------------+--------------------------+
|      KPI Card        |      Category Slicer      |
|  Total Sales Amount  |                          |
+----------------------+--------------------------+
|          Product Sales Details Table             |
|                                                  |
+--------------------------------------------------+

Interactivity

The Category Slicer filters the Product Sales Details Table by product category. Because the relationship is single direction from Products to Sales, filtering by category affects sales data shown in the table.

If the relationship direction is changed to both directions, selecting a product or sales amount in the table can filter the slicer and other visuals, enabling two-way filtering.

Self Check

Task: Add a filter selecting the category Furniture in the Category Slicer.

Question: Which products and sales amounts appear in the Product Sales Details Table, and what is the updated Total Sales Amount in the KPI Card?

Answer: The table shows only products in Furniture category: Chair, Desk, and Lamp. Sales amounts for these products are 450 (Chair) + 500 (Desk) = 950. The KPI Card updates to show Total Sales Amount = 950.

Key Result

Dashboard shows total sales and product sales details with filtering affected by relationship direction between Products and Sales tables.