Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

What is the best way to use extremely large data sets to show summary visuals and have drill through

We have a situation where we we have tables that we can query to provide summary information for visuals such as pivots and charts, but then want to be able to have users get into the details if they want based on a certain line of business,  quarter, etc. Currently this is done with  the main query being used in excel, and then if a user double clicks on the pivot table it calls an SP that then returns the detailed information in a new workbook. We want to move this online though. Does PowerBi offer anything that could do something like this?

1 ACCEPTED SOLUTION

No problem.  Perhaps this will help, it is a presentation from Microsoft at the Business Application Summit 2019 looking at aggregations over a 1 trillion row data set.

https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-2172

I have not done any work with the aggregations myself but this is along the lines of what you are looking for yes?

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

Hello @Anonymous 

Yes, PowerBI has a drillthrough capability that allows you to set up a detailed report, say at the order line level with product, category, price, etc then you set that report as a drillthrough target and add the fields that will be getting applied as filters.

The fields you add as the drillthrough fields are the fields you will be summarizing by on your summary report, line of business, quarter, etc.  When a user is looking at the summary report they can right click on a visual to access the drillthrough feature. 

If they right clicked on a piece of a visual that was displaying Q1-2019 sales for the computers line of business, those two filters would be carried through to the drillthrough but you would be shown the detailed lines.

Here is an article from Microsoft about drillthrough: https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

Anonymous
Not applicable

Sorry, I should have specified that I am familiar with the regular drillthrough but the detailed data set is far too large to load all of it in at once. I would be best if the only information that was loaded was the information specified but selections from the summary. That is why our excel passes information to the SP so it only pulls back what is needed.

No problem.  Perhaps this will help, it is a presentation from Microsoft at the Business Application Summit 2019 looking at aggregations over a 1 trillion row data set.

https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-2172

I have not done any work with the aggregations myself but this is along the lines of what you are looking for yes?

Anonymous
Not applicable

Thank you for this. I've looked into it some more and it looks like it would work for our use case.

The only other thing I have heard of that sounds vaguely like what you are looking for is paginated reports now support parameters in the URL.  Not sure if this is more like what you need but I thought I would mention it.

https://powerbi.microsoft.com/en-us/blog/url-parameters-for-paginated-reports-are-now-available/

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.