Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
5 | |
5 | |
3 | |
2 | |
2 |
User | Count |
---|---|
9 | |
7 | |
5 | |
4 | |
4 |