Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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.
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.
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |