topic Re: How to create dynamic filter in DAX query in DAX Commands and Tips

How to create dynamic filter in DAX query

bhavya11 — Mon, 13 Jan 2025 19:24:10 GMT

Hello,

I want to dynamically filter my data in PBI Report Builder using DAX expression. The columns in the filter will be added dynamically based on what the user selects in the report.

It is a embedded paginated report with a PBI Semantic model as it's dataset. The PBI Semantic Model dataset is created using DirectQuery.

I am sharing the semantic model between PBI Report and Paginated Report. Paginated Report is required to export the tabular report data.

I am using the following DAX :

DEFINE
VAR filters = IF(NOT(ISBLANK(@param1)), "MyTable[col1]=" & @param1, BLANK())

EVALUATE
FILTER(MyTable, filters)

I get the following error:

The query contains the 'param1' parameter, which is not declared.

Please note that I have created a Query Parameter named param1 in the DataSet properties and associated it with respectvie report parameter. The report parameters are passed to the report in the payload.

Need help.

Thank you

Re: How to create dynamic filter in DAX query

Anonymous — Tue, 14 Jan 2025 06:40:27 GMT

Hi bhavya11 ,

"MyTable[col1]=" & @param1

This part looks like text, and the second argument to filter needs to be a boolean expression.

FILTER function (DAX) - DAX | Microsoft Learn

Please try:

DEFINE VAR param1Value = @param1 VAR filters = IF ( NOT ISBLANK ( param1Value ), FILTER ( MyTable, MyTable[col1] = param1Value ), MyTable ) EVALUATE filters

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: How to create dynamic filter in DAX query

bhavya11 — Tue, 14 Jan 2025 13:42:18 GMT

Hello,

Thank you for the inputs. As I want to add multiple filters dynamically, I chose the following route:

EVALUATE
FILTER(MyTable,
AND(MyTable[col1]=@Col1Value , IF(NOT(ISBLANK(@Col2Value)), MyTable[col2]=@qCol2Value, TRUE().....))

I will also try your syntax.

Re: How to create dynamic filter in DAX query

bhavya11 — Wed, 15 Jan 2025 09:46:09 GMT

Hello,

This one doesn't work. I get the same error @param1 is not defined.