Forum Discussion
How to change Parameter setting using Selected Value from a Slicer Visual
Hello raymondwkmok - thanks for posting to the community. In order for 'bind to parameter' to be visible and work, the tables being filtered by the parameters must be direct query as opposed to import mode. At this time SharePoint is not supported as a direct query source without first syncing it with some other location such as Azure SQL then using that as the source. However, it is still possible for you to utilize parameters to limit the volume of data. Instead of using report slicers to select the values of the parameters, you can use on of these methods below. Please let me know if there is anything else I can help with for this.
From PowerBI Desktop: Home > Transform Data > Edit Parameters
From the PowerBI service: Dataset Settings > Parameters
From Deployment Pipelines: Dataset > Deployment Rules > Parameter Rules
Hi raymondwkmok "Link to Parameter" at the Advanced Setting under the Data Model page only works for direct query, and sharepoint currently do not support that.
Without "Link to Parameter" at the Advanced Setting under the Data Model page, Power BI slicer selection or any dax formula does not change the size of the data retrieved from Power Query. Slicers in Power BI are used to filter data that has already been loaded into the data model, but they do not affect the data retrieval process from the source.
So, you need direct query which is not natively supported by sharepoint. There is a third party tools which can synchronize SharePoint lists with SQL Azure tables. Once synchronized, you can use DirectQuery on the SQL Azure tables in Power BI.
Otherwise you need to change parament through power query or power bi each time you want to filter underlying data set. See image for power bi:
This way you can chage underlying dataset size.
Another way to create dynamic data source is custom Rest API.
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
Hi shafiz_p, thanks for your advie and suggestions.
As mentioned in my above reply to Jennratten, I've adopted the Edit Parameters method with a slight modification using a List for changing the parameters.
The only challenge is the report users cannot change the paramters at Power BI Servie as I did; and so they cannot see details of other stores at the source data.
Again, hope Microsoft could extend the use of Dynamic M Query Parameter to other data source like SharePoint somedays in the future.
Thanks
Raymond