Drillthrough and Cross Filtering in Dynamics Reports

Introduction

Modern business intelligence is not just about displaying static data—it’s about enabling users to explore, interact with, and drill into data for deeper understanding. In the Dynamics 365 ecosystem, and particularly in Power BI reports used within or alongside Dynamics 365, Drillthrough and Cross Filtering are essential features that empower users to analyze data from multiple perspectives without overwhelming dashboards with all possible information upfront.

In this article, we’ll explore the concepts of Drillthrough and Cross Filtering, understand how they function in Dynamics-integrated Power BI reports, walk through configuration steps, and highlight best practices for building intuitive, interactive reporting experiences.

1. What Is Drillthrough in Power BI?

Drillthrough allows report viewers to right-click a data point and navigate to a dedicated page that provides detailed context about that selection.

For example:

From a report showing total sales by region, a user can drill through to a page with customer-level sales data for just that region.

Drillthrough enhances user experience by:

Providing focused, context-rich information.
Reducing clutter on the main report page.
Allowing interactive navigation without code.

Use Cases in Dynamics 365:

Sales managers drilling from pipeline summary to individual opportunity details.
Customer service reps drilling from case volume by category to unresolved case lists.
Finance teams drilling from expenses summary to detailed transaction reports.

2. What Is Cross Filtering?

Cross Filtering in Power BI refers to the dynamic interaction between visuals on a page. Selecting a value in one visual filters other visuals based on related data.

For example:

Clicking on “East Region” in a map visual filters the bar chart to only show sales reps from that region.

Cross filtering improves storytelling by:

Highlighting relationships between data elements.
Enabling dynamic, real-time interaction.
Supporting exploratory data analysis.

Use Cases in Dynamics 365 Reports:

Selecting a product category to filter customer purchase behavior.
Clicking on a sales rep to see their pipeline across different stages.
Filtering open cases by priority level to analyze agent workloads.

3. Configuring Drillthrough in Power BI for Dynamics 365 Data

Let’s walk through how to implement Drillthrough functionality step-by-step.

Step 1: Set Up the Drillthrough Page

Open Power BI Desktop.
Add a new page and rename it, e.g., “Opportunity Details.”
Drag the field you want to drill through by (e.g., Opportunity Owner) into the Drillthrough filters well.
Design the page layout with visuals that provide deep-level data (tables, KPIs, charts).

Step 2: Source Report Page Setup

On your main report page (e.g., pipeline summary), right-click a data point (such as a salesperson’s name), and Power BI will show an option to Drillthrough to your detailed page.

Step 3: Add a Back Button

Insert a back button (Insert > Buttons > Back) on your drillthrough page to let users easily return to the previous page.

Optional: Add Dynamic Titles

Use measures with SELECTEDVALUE() to show which data is being analyzed.

"Details for " & SELECTEDVALUE(Opportunities[OwnerName])

Real-World Example: Sales Pipeline

Main Page: Bar chart showing opportunities by stage.

Drillthrough Page: Shows list of opportunities, contact details, estimated close dates, etc., for the selected stage or owner.

This allows a sales manager to go from a high-level pipeline to actionable, owner-specific data with a couple of clicks.

4. Configuring Cross Filtering in Power BI

Cross filtering is typically enabled by default, but you can customize interactions to tailor user experience.

Step 1: Enable Cross Filtering

Add multiple visuals to a report page.
Select a visual (e.g., a pie chart).
Go to Format > Edit Interactions.
You’ll see icons appear over other visuals:
- Filter (filters visual based on selection).
- Highlight (partial filter).
- None (no interaction).

You can control which visuals are affected and how.

Step 2: Use Bidirectional Filtering (Advanced)

For scenarios where filters need to flow in both directions, you can enable bidirectional relationships in the data model.

Example:

When filtering customers, their related orders also filter products.

Caution: Overuse of bidirectional filtering can impact performance and introduce ambiguity.

5. Drillthrough vs. Cross Filtering: Key Differences

Feature	Drillthrough	Cross Filtering
Purpose	Navigate to detailed report pages	Interact between visuals on the same page
User Action	Right-click + select drillthrough target	Click/select a data point
Page Context	Opens a separate page	Stays on the current report page
Complexity	Requires configuration of drill fields	Mostly automatic, customizable interactions
Best For	Deep dives and contextual detail views	Quick filtering and data exploration

6. Enabling Drillthrough in Dynamics 365 Dashboards

In Dynamics 365, Power BI reports can be embedded into dashboards, offering users direct access to drillthrough-enabled reports.

Steps to Embed Power BI Reports in Dynamics:

Publish your Power BI report to a workspace.
Go to Dynamics 365 > Settings > Administration > System Settings.
Enable Power BI integration.
Create a new Power BI dashboard in Dynamics.
Add your report page with drillthrough enabled.

Now, users in Dynamics can right-click and drill through without leaving the application.

7. Advanced Techniques

a. Dynamic Drillthrough with Disconnected Tables

You can create drillthrough pages that respond dynamically based on user selection from slicers or unrelated tables.

Example:

A slicer lets users choose the drill context (e.g., “Region” or “Owner”).
A dynamic drillthrough page adjusts based on selected criteria using DAX and bookmarks.

b. Use of Bookmarks for Simulation

Simulate drillthrough using bookmarks and buttons:

Create filtered views using slicers.
Set up bookmarks for each scenario.
Add buttons to navigate to each filtered bookmark.

Great for presentations or restricted navigation environments.

c. Conditional Drillthrough

Using ISFILTERED() or HASONEVALUE() in DAX, you can control visuals to only display when a filter is applied.

IF(HASONEVALUE(Opportunities[Owner]), [Measure], BLANK())

Helps avoid showing data when no valid context is passed via drillthrough.

8. Security Considerations

When using Drillthrough and Cross Filtering in Dynamics 365 reports, data security must remain a top priority.

Use Row-Level Security (RLS):

RLS ensures users only see data they are authorized to access—even when drilling through.
Drillthrough filters respect RLS rules.
Use USERPRINCIPALNAME() or USERNAME() to tie data to users.

Best Practice: Always Test

Test drillthrough for each security role.
Verify that filters do not expose unauthorized data.
Consider dummy users for impersonation during testing.

9. Performance Optimization Tips

Drillthrough and cross filtering, if not managed well, can impact report performance:

Limit visual count on drillthrough pages.
Avoid too many fields in drill filters.
Optimize DAX measures to reduce calculation overhead.
Use summary tables when possible instead of raw transactional data.

10. Real-World Scenario: Customer Service Dashboard

Imagine a dashboard for a service manager that includes:

Case volume by category.
Agent performance.
Average resolution time by priority.

Use Drillthrough to:

Dive into unresolved cases for each agent.
View escalation history of high-priority cases.

Use Cross Filtering to:

Select a category and see volume over time.
Click on an SLA breach bar and watch all other visuals react instantly.