Forum Discussion
How to pass selected Field Parameter to Query M SQL Query
Hi there
I have successfully used parameter binding on selected Values when the values are selected through a slicer on a 'hand-made' parameter table. The parameters are passed to the SQL query (direct query mode) where I insert the the bound parameter into the SQL-Query.
What I am trying to achieve now puzzles me: I have a FIELD PARAMETER slicer & table. Meaning I created a field parameter, where the user may select the column by which to visualize the bar chart. Let's say the values are Sales in $ and the columns to choose from are by Brand, Country or "Brand Partner Status".
I am not able to pass the selected Column in the Field Parameter Slicer to the SQL query.
- If I bind the ParamTable[Param Field] where the values are like 'NAMEOF('SalesTable'[BRAND]) then I get an error in my visualisation (something's wrong with the column...).
- When I try to select the ParamTable[Param] Column (first column of the generated table) or the ParamTable[Param Order] then I just always get the standard parameter value as result.
- When I try to filter a second 'hand made' table called SELECTEDCOLUMN with the same options according to the slicer using a relationship and try to use QueryM-Function Table.FirstValue(), then I just get the first value of the table SELECTEDCOLUMN without the applied filtering in the UI
Does anybody know how to achieve this?
Hi Que_Ry_Sa
Passing a dynamic Field Parameter to a native SQL query isn't straightforward because SQL expects fixed column references, while Field Parameters are evaluated at runtime on the Power BI side.Unfortunately, Field Parameters can't directly influence the structure of a SQL query, because M/Power Query (and SQL behind it) doesn't interpret dynamic column names from slicers or field parameters the way a DAX visual would.
Here’s a possible approach you can try (I've used this successfully in similar setups):
- Create a parameterized SQL view in your SQL Server (or underlying source). This view takes a string input for the column name and applies a CASE or dynamic SQL inside the view logic itself.
- In Power BI:
- Keep using your Field Parameter slicer to let users pick the column.
- Create a mapping table that translates your slicer choices (e.g., "Brand", "Country") into the actual SQL-safe column names.
- Use a disconnected table and capture the selected column using a measure like:
SelectedCol = SELECTEDVALUE(ParamTable[Param Field])- Pass that value into a M parameter using Power Query (this requires breaking query folding and turning it into a native query), like:
SELECT CASE WHEN @SelectedColumn = 'Brand' THEN Brand WHEN @SelectedColumn = 'Country' THEN Country WHEN @SelectedColumn = 'PartnerStatus' THEN [Brand Partner Status] END AS DynamicColumn FROM SalesTable- Connect your slicer to the parameter via a what-if parameter or use Table.FirstValue() carefully with a properly filtered table not the field parameter table.
5 Replies
Hi Que_Ry_Sa
Passing a dynamic Field Parameter to a native SQL query isn't straightforward because SQL expects fixed column references, while Field Parameters are evaluated at runtime on the Power BI side.Unfortunately, Field Parameters can't directly influence the structure of a SQL query, because M/Power Query (and SQL behind it) doesn't interpret dynamic column names from slicers or field parameters the way a DAX visual would.
Here’s a possible approach you can try (I've used this successfully in similar setups):
- Create a parameterized SQL view in your SQL Server (or underlying source). This view takes a string input for the column name and applies a CASE or dynamic SQL inside the view logic itself.
- In Power BI:
- Keep using your Field Parameter slicer to let users pick the column.
- Create a mapping table that translates your slicer choices (e.g., "Brand", "Country") into the actual SQL-safe column names.
- Use a disconnected table and capture the selected column using a measure like:
SelectedCol = SELECTEDVALUE(ParamTable[Param Field])- Pass that value into a M parameter using Power Query (this requires breaking query folding and turning it into a native query), like:
SELECT CASE WHEN @SelectedColumn = 'Brand' THEN Brand WHEN @SelectedColumn = 'Country' THEN Country WHEN @SelectedColumn = 'PartnerStatus' THEN [Brand Partner Status] END AS DynamicColumn FROM SalesTable- Connect your slicer to the parameter via a what-if parameter or use Table.FirstValue() carefully with a properly filtered table not the field parameter table.
Selected values from field parameters can't be passed into M SQL queries. While M influences the semantic model, anything done in the report designer stays within the designer - it doesn't flow back into the query layer. Only parameter values in the query editor can be passed on to a M SQL query.
