How to change Parameter setting using Selected Value from a Slicer Visual

[raymondwkmok]

Hi there,

I've a created PQ in Power BI in which I'd like to filter the source data files based on the "File Name" so as to reduce the source data size, due to the huge number of files involved (over 120,000 csv files for one month).

In the PQ, I created a Parameter "Store_Selected" for filtering the files in the SharePoint folder where the source data located; and also created a new Table "Store_List" that keeps the distinct value of stores in column [Store]. 

I however cannot connect the column [Store] to the Parameter "Store_Selected" because the "Link to Parameter" was not show up at the Advanced Setting of [Store] under the Data Model page.

Then I created a DAX Measure "Store_Selected" based on the selected value of a Slicer on [Store], and attempted to put the Measure "Store_Selected" at the Parameter setting og "Store_Selected" ... but as expected it doesn't work.

May I know in what way I can resolve the issues of changing Parameter setting dynamically based on the selected value of an existing Table please.

Thanks
Raymond

 

[jennratten · Accepted solution]

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

[raymondwkmok]

Hi Jennratten, thanks for the advice and much appreciate the great suggestions.

Based on your advice, I've created a list of "Store Names" at Power Query then use it at the PQ Parameter; Now I can select store name from a dropdown menu while I'm changing the store name at Parameter Edit of Power BI Desktop.

Unfortunately, the users of my BI Report will not be able to change the parameter at Power BI Service after I published the report.

If I have no permission to use SQL Server, I'll have to wait for Microsoft extending the use of Dynamic M Query Parameter to other data source like SharePoint.

Thanks
Raymond
  

[shafiz_p]

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

[raymondwkmok]

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

Preview

[PwerQueryKees]

The powerquery looks fine. What is the error you get?

And it would be helpfull to see the result of the #"Selected Store" step. Just put in the "in" clause....

I have no DAX experience, so can't help you there.