In Power BI, you may want to pass the filter value user selected as parameter to your direct query data source to SQL Server. You can achieve this using dynamic M parameter.
Please note, Bind to parameter option will NOT be available in Power BI unless a Direct Query query presence.
In Power Query Editor, create a M parameter using Manage Parameters. The data type for the parameter needs to match with the type of data you try to bind with.
Modify your Direct Query using Advanced Editor and concatenate the parameter into your query. If your parameter data type is not text, you might need to convert it to text while doing concatenation. For example, text.from(OrderDate).
If you want to bind the parameter to an existing table column, in Model View, select the column and go to Properties –> Advanced and pick the parameter to bind with.
If you want to let user enter the parameter value instead of pick value from a table, we can bind the parameter to a dumy table. Create a dumy table using following M query will give you an empty table. Make sure the column data type match parameter data type. Bind the parameter to this table and then user can use the Advanced filtering option in the report to enter any value they need.
let
Source = #table(
type table [YourColumnName = text],
{}
)
in
Source
Reference:
Passing Any Arbitrary Value From A Power BI Report To A Dynamic M Parameter