Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I've got a need to display a report containing data (using DirectQuery). My datasource is a SQL view.
Is it possible for me to pass a parameter(s) to a WHERE clause when rendering my report so that my dataset will only be limited to specific rows?
So for example if my SQL view looked like this:
And I only wanted my data set to contain rows with a RecordId of 346, 123 and 458, is it possible for me to do this? This has to be dynamic because when rendering the same report elsewhere I'd perhaps only want to show records 328 and 995.
You can try to use filter when embedding in the Javascript API. Check the wiki.
const basicFilter: pbi.models.IBasicFilter = { $schema: "http://powerbi.com/product/schema#basic", target: { table: "Store", column: "Count" }, operator: "In", values: [1,2,3,4] }
A quick demo for your reference.
<html> <script src="https://microsoft.github.io/PowerBI-JavaScript/demo/bower_components/powerbi-client/dist/powerbi.js"></script> <script src="https://microsoft.github.io/PowerBI-JavaScript/demo/bower_components/jquery/dist/jquery.js"></script> <script type="text/javascript"> window.onload = function () { var IamAFilter = { $schema: "http://powerbi.com/product/schema#basic", target: { table: "Questions", column: "site" }, operator: "In", values: ["stackoverflow"] } var embedConfiguration = { type: 'report', accessToken: 'YOUR TOKEN HERE', id: 'b7441d21XXXXXXXc5bd6426d', embedUrl: 'https://app.powerbi.com/reportEmbed?reportId=b7441d21-XXXXXXXX7c5bd6426d', filters:[IamAFilter], // the filters is an array here, you can add more filter like [filter1,filter2,filter3] settings: { filterPaneEnabled: true //hide the filterPane so that your user can't change the filter to see more data, this is not a strong security, anyone who's familar with javascript can bypass it } }; var $reportContainer = $('#reportContainer'); var report = powerbi.embed($reportContainer.get(0), embedConfiguration); } </script> <div id="reportContainer" powerbi-settings-nav-content-pane-enabled="true" powerbi-settings-filter-pane-enabled="true"></div> </html>
By the way, it is not a good idea to use the filter to stop people seeing data, for security, I'd suggest you use RLS in Power BI Embedded.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
4 | |
4 | |
3 | |
3 | |
2 |