Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
bhavya11
Regular Visitor

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

1 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.

View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

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

Hello,

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

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.