Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Our users would like to be able to use cross-report drillthru to review detail records from an enormous, 350 col wide, 900 million row table.
Our 'header' report works well in import mode and we have created keys and other efficiencies so that records our our desired grain can be loaded quickly from the database. We are close to a working solution.
However, the report cannot be effectively deployed -- the direct query attempts to bring back an outrageous amount of data because it always tries to load the full, unfiltetered dataset from SQL before there is a chance to filter the query according to the preferred drill-through parameters.
Has anyone managed to create leaf level reports over small sections of large tables? What strategies did you use? Of principal importance to our stakeholder is that the detail can be reached from a parent-level report so that users don't constantly have to reconfigure slicers/filters on reports over large datasets.
Hey, appreciate this a few years old now, but did you ever get it to work? I'm just looking at a similar situation now.
We prototyped a solution where the 'detail' report was kept in a parameterized paginated report that would run a restricted sql query against the main dataset. The PowerBI report included links to the paginated report that included URL parameters. It worked well for desktop users, but those URL parameters were not supported in the PowerBI mobile app, and the solution was abandoned.
The business group decided the problem wasn't suitable for PowerBI and took the work to a different technology.
@RBunting you can also look at composite and aggregation method
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
This is pretty high level but you can use the following options:
1) Create a SQL query in advanced editor - click on edit queries, then advanced editor
Ex from one of my tables:
let
Source = Sql.Database("yourdatabase-sql.database.windows.net", "yourdatabase", [Query="SELECT * FROM yourtable AS t WHERE t.LOB = 'filterdataonthis'"]),
#"Removed Duplicates" = Table.Distinct(Source),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Role From", "Role"}, {"Full Name", "Name"}, {"Employee ID Number", "Emp ID"}})
in
#"Renamed Columns"
2) Create a new summary table in Sql server - We had a similar issue with our time data, so I wrote a stored proc in SQL Server that created a new summary table - so that power bi would hit the new table and only download the data I needed to download
3) I don't suggest using direct query since it attempts to create a live link with your database, instead use import and then run refreshes at more optimal times (like overnight or every few hours)
4) Use incrmemental refresh to only update new or specific data
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |