Advice on how to model Summary + Details pages with large data

[yurcus]

Hello All,

I am working on a dashboard prototype and was hitting a few roadblocks. 

First page on my dashboard shows summary data aggregated at daily level (charts, trendlines, etc). Since this data is aggregated on daily level its easy to work with in the Import mode. However once user picks a day (from a chart) I want to show the full data that contributes to the aggregated value. This data can be very large and using Import model to keep all the detail data rows would make the pbix file size grow to ~20GB.

Ideally, in this case the detailed page would use datasets that are configured as DirectQuery and would request data for one day when requested. 

Since Import and DataQuery models cannot be combined, i wonder if there is a different way to model this scenariou, maybe through linking two separate reports? (e.g. maybe similarly to SSRS we can include a subreport in the dashboard?)

thanks

Yuri

[v-shex-msft]

Hi @yurcus,

>>Since Import and DataQuery models cannot be combined, i wonder if there is a different way to model this scenariou, maybe through linking two separate reports? (e.g. maybe similarly to SSRS we can include a subreport in the dashboard?)

Current power bi not support this requirement, I also check for this but haven't find any documents mentioned.
Maybe you can try to submit an idea for this requirement.

Regards,

Xiaoxin Sheng

[lndnbrg · Accepted solution]

Hi! I am quite sure you have thought about that, but is there a way to reduce the detail data t.ex. by excluding unnecessary columns?

If not, what is stopping you from using direct query for your entire report?

You may split it up into two separate reports and link them together using URL parameters. By that you can link from the overview into the details report and hand over the ID.

This may help you: https://guyinacube.com/2017/03/filter-power-bi-reports-using-url-query-string/

[yurcus]

Thanks Indnbrg for the suggestion!

Imported data sets allowed calculated columns (that are limtied in DQ) and caching of the data. This would reduce the load on the server since most of the time users would be spending on the summary page adjusting slicers and ocasionally switching to the details view. Hence my attempt to combine Imported data with DQ.

However linking two separate reports with URL parameters should be good as a workaround thanks a lot for the link