Power BI Embedded - Filtering dataset based on an array (WHERE clause)

MarkWalsh — Mon, 27 Feb 2017 13:39:52 GMT

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.

Re: Power BI Embedded - Filtering dataset based on an array (WHERE clause)

Eric_Zhang — Tue, 28 Feb 2017 06:17:57 GMT

@MarkWalsh

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.

topic Power BI Embedded - Filtering dataset based on an array (WHERE clause) in Developer

Power BI Embedded - Filtering dataset based on an array (WHERE clause)

Re: Power BI Embedded - Filtering dataset based on an array (WHERE clause)