Forum Discussion

bhavya11's avatar
bhavya11
Regular Visitor
1 year ago
Solved

How to create dynamic filter in DAX query

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

DAX
DirectQuery
dynamic filter
Paginated Report
  • bhavya11's avatar
    bhavya11
    1 year ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      bhavya11
      Regular Visitor
      1 year ago

      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.

    • bhavya11's avatar
      bhavya11
      Regular Visitor
      1 year ago

      Hello,

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