Power BI - Bind column to Parameter

[coletta625]

 I created a parameter with values tied to another table called SortBy which holds the valid list of parameters.

I have a slicer on the canvas tied to Sort Value

No action is taken when I select a value from that slicer.  

I've been reading about binding a field to a parameter, but I do not have that option in my Desktop version modeling - advanced settings nor do I see in Power BI service editor.

Field parameters as a Preview Feature is checked in Options.

How can I expose the parameter the user so they can dynamically select a value that will refresh the visual with the correct passed parameter?

[Anonymous · Accepted solution]

Hi @coletta625 ,

According to your description, you want to do dynamic m-parameter binding for sql queries in power bi desktop.
First, you need to make sure that you are connecting to sql as a direct query.

Secondly, create a new parameter in power query.

Again, open the advanced editor and replace the parameter with the reference where it needs to be changed dynamically.

Then, go back to the power bi desktop and create a parameter table and bind it to the m parameter.

Table = 
{
    "data_refreshTest",
    "data_refreshTest_2",
    "data_refreshTest_3"
}

Finally, create a slicer with the data from the new table so that you can make dynamic parameter change.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[sasdfasdfsad]

Does this not work with a stored procedure then?

It has to be a query or a view, it can't use parameters that are bound with stored procedures?

[bchager]

@coletta625 Are you referencing the parameter value in a measure that's used for your visualization?

[coletta625]

I can't use a measure because the ultimate goal of this parameter is to pass to a SQL Stored Procedure in Power Query like this:

Let

Source = Sql.Database("jobboss", "ems_Sql", [Query="EXEC sp_usr_ShopTracker '" & SelectedParameter & "'"

I can't use a calculated column because that cannot hold the SELECTEDVALUE of a slicer.  I've even tried creating a manual table to hold a value to pass, but I cannot figure out a way to allow the user to change this value.

let
    SelectedParameter = Table.FirstValue(ParamTable),
    Source = Sql.Database("jobboss", "ems_Sql", [Query="
EXEC sp_usr_ShopTracker '" & SelectedParameter & "'"

Short of creating 9 queries and visuals with a hardcoded parameter value, I am out of ideas.