drill through from a report in one pbix file to a report in another pbix file

[MinhHaiCH]

Hi all,

I am new to PowerBI and I have the following problem:

- I have two tables with traffic count. One table includes traffic count stations with StationID as key and other data. The second is traffic data with rowID, StationID, time and other columns. The second table contains millions of data lines and keeps growing.

- I can easily create a report allowing users to choose a station, a date and then the report shows statistics for that station on that day. However, all data needs to be loaded into the workspace and it takes hours to lead all the data.

One of the solutions is to use "Dynamic M query parameters" to allow users to select a station and a date and then the corresponding report will show. Then I have problem with limited visual possibility in case I want to enlarge the users' choices (more choices than juste the station and the date).

I am wondering if I can create a first report in a PBIX for all the possible choices. Then selected values are transfered to a second report in a second PBIX file which is the final report. 

Does this method exist in PowerBI Desktop? Is there any other way around (apart from "Dynamic M query parameters")? Thanks for your help.

[v-jialongy-msft]

Hi @MinhHaiCH 

Here is the official tutorial documentation for creating reports using composite models. With composite models, you can import your first table using the import mode, and then connect to tables with larger data volumes using the DirectQuery mode. This approach helps avoid loading large amounts of data, significantly reducing report loading times.

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FarhanJeelani · Accepted solution]

Hi @MinhHaiCH ,

To address your issue, here's what you can consider:

1. Dynamic M Query Parameters are great for optimizing performance with large datasets, but they do limit the flexibility of user selections.

2. The method you mentioned—creating a first report to pass selected values to a second PBIX—is not natively supported in Power BI Desktop. However, you could explore Power BI dataflows or shared datasets. These allow multiple reports to connect to the same data model, potentially enabling your workflow with some adjustments.

3. Another approach could be to use DirectQuery mode with parameterized filters. This way, only the required data is queried, reducing load times significantly.

4. If you want to offer more filter options while keeping performance high, consider pre-aggregating your data at relevant levels (e.g., station and date) and then connecting your reports to this pre-aggregated dataset.

Let me know if you’d like more details on any of these solutions!

Please accept this as solution if it helps. Appreciate Kudos.

[MinhHaiCH]

Hi @FarhanJeelani ,

Thank you very much for your suggestions.

As discussed, solution 1 is not preferable. Solution 4 relates to another person who takes care of database. I will talk to that person to see whether he is able and ready to help.

Please help me on solution 2 or solution 3 knowing that I can only do this on PowerBI Desktop. The option with a data server or PowerBI Service is not available at this moment. So maybe DirectQuery mode is the solution for me. Thank you in advance!