How to pass the dynamic parameters in selectcolumns dax query in Paginated Reports

[Koritala]

Hi, I was struggling to make dynamic query using the selectcolumn function where I want to pass the report parameters dynamically in paginated report. 

I have written DAX like bleow and it is validating when I did validate in the dataset properties window. But when I run the report as whole it is giving error.

EVALUATE

( SELECTCOLUMNS(

FILTER(Table name, table name[filter column name]="@levelcode"),

"custom column name", table name[column name])

I have defned the respective parameter in Dataset properties as well.

I am getting error like "The parameter<ccon>levelcode</ccon> is not reffered in the query.

Please suggest in fixing this as early as possible.

Thanks,

Koritala

[v-priyankata]

Hi  @Koritala 

Thank you for using Microsoft Community Forum.

When using DAX in Paginated Reports and you want to pass parameters dynamically (like a filter value), you must build the DAX query as a string expression using the fx (expression) editor. Here's how:

Correct Way Using VB.NET Expression, If your parameter is ctype (a text value), use this syntax:

vb ="EVALUATE SELECTCOLUMNS( FILTER( 'Query1', 'Query1'[contributor_type] = """ & Parameters!ctype.Value & """ ), ""PolicyNumber"", 'Query1'[policy_number], ""ContributorType"", 'Query1'[contributor_type])"

Key Points : 

• vbCrLf adds line breaks to format the query properly (important for correct parsing).
• Triple quotes """ are used to inject a string parameter into the DAX.
• Do not use @@ctype or placeholders directly inside the DAX — it won’t work
• This builds a valid DAX query at runtime, such as:

EVALUATE SELECTCOLUMNS(FILTER('Query1','Query1'[contributor_type] = "EMR"),"PolicyNumber", 'Query1'[policy_number],"ContributorType", 'Query1'[contributor_type])

Troubleshooting Tips :
1.If you're still getting a syntax error, double-check for extra commas or missing quotes.
2.Make sure your parameter (ctype) is correctly defined as a report parameter and bound in the dataset properties.
3.Use numeric parameters without quotes.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Koritala]

Hi Priyanka, 

Here is my final query and error which I am getting when running the report. At the designer level query is getting validated. But when I run the entire report, it is giving the error. plz correct me if any code issues you see.

[v-priyankata]

Hi  @Koritala 

Problem In executed query window, the expression starts with: 

vb="EVALUATE ... This is incorrect. vb= is not a valid syntax in the query itself. It was just a note in my explanation indicating the use of VB.NET expression format (Visual Basic line breaks, etc). But in the actual query box, do not write vb= at all.

Corrected Working Expression (for text parameter like ctype):
Go to Query > fx button and use this exact expression:

="EVALUATE 
SELECTCOLUMNS(
    FILTER('Query1','Query1'[contributor_type] = """ & Parameters!ctype.Value & """),
    ""PolicyNumber"", 'Query1'[policy_number],
    ""ContributorType"", 'Query1'[contributor_type])"

Note : Do not add vb= at the start — that breaks the syntax.

vb (used in earlier examples) is just for readability — not mandatory.

This format builds a valid DAX string query using your parameter value (Parameters!ctype.Value).

Will it work for Redshift views?
No — this approach is specific to Power BI Paginated Reports using DAX with an Analysis Services (like Power BI Dataset or SSAS) data source.

If you're querying Amazon Redshift, you'll be writing SQL, not DAX — so parameters would be passed using the syntax supported by your SQL provider, like: sql

SELECT policy_number, contributor_type
FROM your_view
WHERE contributor_type = @ctype
And make sure @ctype is defined as a report parameter.

May I know, you're targeting a Power BI Dataset or Redshift?

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Koritala]

Hi Priyanka,

Thanks for your response. 

Here, I am attaching my screenshots of the attempt to connect to the Redshift database view via ODBC connection. I am successful to pass the single value selection in  report slicer. But I am struck with the syntax where I need the multiselect in the report slicer. Could you please share the SQL format to pass the multiple values into slicer.

For you reference, here I am mentioning my query details and prompt details. Plz consider this as priority as I need to give solution in next 2 days.

Thanks,

Koritala

[v-priyankata]

Hi @Koritala 
Sorry for the delay in response, 

To set up a Power BI Report Builder report that supports a multi-select parameter and avoids the "forward dependency" error, follow this summarized process:

First, create a dataset called LevelCodeList that retrieves the list of possible values for the parameter. This dataset must not reference the parameter itself. Use a simple query like SELECT DISTINCT level_code FROM sample_scheme_data ORDER BY level_code. This will ensure the parameter can load independently without causing a circular reference.

Next, add a report parameter named level_code. Set its data type to Text and enable the Allow multiple values option. Under the "Available Values" section, configure it to get values from the LevelCodeList dataset using level_code as both the Value and Label fields. This parameter now has a valid source of values without referencing itself.

Then, create your main data dataset this is where the report will pull the filtered records. Instead of using a static SQL query, use a dynamic SQL expression via the fx button in the Query pane. Write the expression as:
SELECT * FROM sample_scheme_data WHERE level_code IN (@level_code)
This dynamically injects the selected parameter values into the SQL IN clause in the correct format.

Finally, insert a table into your report and bind it to the main dataset. Add the required fields such as level_code, status_flag, scheme_id, and scheme_number. When you preview the report, selecting multiple values from the level_code parameter should now correctly filter the results without error.

Thanks.

[Koritala]

Hi Priyanka, this approach alreday tried and not worked.

IN clause will not work with redshift 

[v-priyankata]

Hi @Koritala 
We understand your issue and regret the inconvenience caused. We acknowledge your requirements and have tried to provide all relevant guidance. If the issue still persists,

Please consider raising a Microsoft support ticket for further investigation. You can explain all the troubleshooting steps you have taken to help them better understand the issue.
 

You can create a Microsoft support ticket with the help of the link below:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks.

[Koritala]

Hi Priyanka, 

Really appriciate your support as of now. 

Thanks,

Koritala

[Koritala]

Hi Priyanka, Thanks for your response.

One thing I want to check in your reference code, can you confirm to me that in the expression starting "vb=" is mandatory?

Also, your code will work for the Redshift views as well? 

Will appriciate if you can respond asap.

Thanks,

Koritala

[Koritala]

Hi,

I tried as per your suggestions. But I am getting following error. 

For reference, here I am mentioning the screenshots.

Please let me know where is the issue.

Thanks for your response.

Error:

Koritala

[DataNinja777]

Hi @Koritala ,

In Paginated Reports, when using DAX with dynamic parameters, you cannot write the parameter reference like "@levelcode" inside the DAX query directly because it treats it as a string literal, not a parameter. Even though it validates in the designer, it will fail at runtime with an error saying the parameter is not referred in the query. The correct way is to use the expression editor in the dataset properties and build the DAX as a VB.NET string expression. You can do this by clicking the fx button next to the query and writing the entire DAX as a string, inserting the parameter using concatenation. For example, if your parameter is named levelcode and is a text value, the correct expression would be:

="EVALUATE SELECTCOLUMNS(
    FILTER('Table name', 'Table name'[filter column name] = """ & Parameters!levelcode.Value & """),
    ""custom column name"", 'Table name'[column name]
)"

If the parameter is numeric, you should not wrap it in quotes:

="EVALUATE SELECTCOLUMNS(
    FILTER('Table name', 'Table name'[filter column name] = " & Parameters!levelcode.Value & "),
    ""custom column name"", 'Table name'[column name]
)"

This way, the parameter is properly injected into the DAX query and the report will run correctly.

Best regards,

[Koritala]

Hi DataNinja 777,

Thanks for your response.

I tried with your code pattern and unfortunately I am getting the error.

For your reference, here I am attaching the screenshots. Please suggest where it went wrong.

Thanks,

Koritala