How to Pass an Excel Cell Parameter to a Power BI Dataset Query
Question details
The user wants to know how to dynamically pass a parameter from an Excel cell into a Power BI dataset query, and understand why the parameter box is grayed out for DAX commands.

- Product
- Excel, Power BI
- Device & OS
- not provided
- Scenario
- Attempting to filter a Power BI dataset connection in Excel by replacing a fixed DAX command text value with an Excel cell reference or named range.
- Observed behavior
- The query parameter box is unavailable for the OLE DB connection, preventing the user from inserting a cell or named range directly into the DAX Command Text.
Verify that your version of Excel supports Power Query and ensure you have the appropriate workspace permissions to query the target Power BI dataset.
Use Power Query Parameters Instead of OLE DB DAX Commands
Since direct OLE DB DAX connections do not support dynamic cell parameters, you must route your query through Power Query to achieve dynamic filtering.
Parameters are generally not available for OLE DB query connections, meaning you cannot normally insert a cell or named range directly into the DAX Command Text in place of a fixed value.
To bypass this limitation, you can build a parameter table in Excel, import it into Power Query as a variable, and use that variable to filter your Power BI connection.
In a blank Excel sheet, create a small two-cell table consisting of a header (e.g., 'FilterValue') and the value you want to pass (e.g., '303'). Format this as an official Excel Table by pressing Ctrl+T.
Select your new table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range'. Once the Power Query Editor opens, right-click the actual value in the table and select 'Drill Down' to convert it from a table to a single scalar value.
Open your main Power BI dataset query in the Power Query Editor. Navigate to the filtering step in the 'Applied Steps' pane, and replace the hardcoded value in the formula bar with the name of your new parameter.
Click 'Close & Load' on the Home tab to return the filtered data to Excel. Whenever you change the cell value in your workbook, simply click 'Refresh All' on the Data tab to update the query.

Utilize Slicers on a PivotTable Data Model
If you just need user-driven filtering without complex Power Query setups, connect to the Power BI dataset directly and use standard PivotTable Slicers.
Try WPS Office for Your Data Analysis Needs
If you frequently work with complex datasets and need a fast, reliable spreadsheet application, WPS Office provides a highly capable, free alternative to Microsoft Office. It features seamless format compatibility and powerful data processing tools in a familiar interface.
- 1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite and follow the simple installation prompts.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx workbooks seamlessly without worrying about formatting issues.
- 3. Analyze Your Data: Utilize built-in PivotTables, advanced formulas, and intuitive data filtering options to process your information effortlessly.

Frequently Asked Questions
Why is the parameter box greyed out for my Excel query?
The parameter box is typically disabled because dynamic parameters are generally not supported for OLE DB query connections using DAX Command Text. You cannot pass Excel cells directly into these specific query types as variables.
Can I use an Excel named range in my DAX command text?
No, a named range cannot normally be inserted directly into the DAX Command Text in place of a fixed value. To achieve dynamic filtering, you must process the named range through Power Query as a parameter.
How can I filter a Power BI dataset based on user input in Excel?
The most effective methods are either importing the user input into Power Query as a parameter to filter the dataset during the query stage, or connecting the dataset to a PivotTable and using Slicers to let users filter the data interactively.




