How to create dynamic filter in DAX query

[bhavya11]

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

[Anonymous]

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

[bhavya11]

Hello,

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

[bhavya11 · Accepted solution]

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.