Power BI’s latest update brings a highly anticipated feature to the forefront: Excel users can now drill through their data for all Power BI semantic models, including Direct Lake and DirectQuery. This advancement removes a significant obstacle for analysts seeking deeper insights without switching data modes or sacrificing security.
Breaking Down Previous Barriers
Historically, Excel’s Show Details feature, which lets users double-click a PivotTable cell to see underlying data, was limited to Import models. The core issue was its reliance on the MDX DRILLTHROUGH statement, leading to errors or empty results when working with Direct Lake or DirectQuery. This restriction forced many teams to adjust their data strategies or accept limited analysis capabilities.
What’s Changed: Uniform Drillthrough for Every Model
Now, Power BI supports drill-through in Excel across all three model types: Import, Direct Lake, and DirectQuery. When connecting to any Power BI semantic model using Analyze in Excel, users can:
- Double-click leaf-level cells in PivotTables to instantly access detail rows
- Retrieve up-to-date, granular data, even from massive datasets
- Benefit from a consistent workflow, no matter the data connection
This overhaul means no more converting models just for detail access. Importantly, drill-through upholds all Row-Level Security (RLS) and Object-Level Security (OLS) rules, so each user only views authorized information. All filters applied in Excel are respected, ensuring your drill-through results remain relevant and precise.
How to Start Using the New Drillthrough
Adopting this feature is simple, especially if you’re already comfortable with Power BI and Excel integration:
- Connect to your Power BI semantic model via Analyze in Excel
- Build PivotTables using supported fields or measures
- Double-click any leaf-level cell to reveal the underlying dataset
The process stays true to familiar Excel workflows, making it easy to use without additional training or tools.
Performance Tips and Considerations
While drill-through brings greater flexibility, performance will vary based on your data source and query complexity. DirectQuery models, for instance, may experience slower response times, especially with extensive or external databases. To maintain smooth performance, consider the size of data being retrieved and optimize queries where possible, especially when working with intricate or large-scale models.
Takeaway: Empowered, Secure Analytics in Excel
This Power BI enhancement empowers analysts and business users alike, enabling granular, secure data exploration directly within Excel, regardless of the underlying model. By supporting drill-through for Direct Lake and DirectQuery models, Microsoft streamlines the analysis process, keeping workflows simple and data protected. It’s a significant step toward truly seamless self-service BI for organizations of every size.
Source: Microsoft Power BI Blog
Excel Drillthrough for All Power BI Models: A Game-Changer for Analysts