topic Re: Parameters in Report Builder (DAX query from Power BI Report) in Report Server

Parameters in Report Builder (DAX query from Power BI Report)

niki_b — Tue, 29 Jun 2021 22:07:03 GMT

I am copying the dax query from PowerBI Performance analyser to create a report in Report Builder. The report works fine but I would like to add parameters just like slicers in PowerBI Report. I tried adding parameters manually but it does not work. Is there any way to make changes to my query.

I even tried this...

TREATAS({@param}, 'table'[Column])

If anyone has any leads, it will be highly apppreciated.

I have tried the links to the articles posted in previous solutions. I was not able to figure it.

Thanks a lot.

Re: Parameters in Report Builder (DAX query from Power BI Report)

AbhiSSRS — Wed, 30 Jun 2021 09:39:35 GMT

You can add parameters in Report Builder Add Params section and use them in DAX too. Could you please post more details like the DAX you are trying to insert these in and where have you added these params.

Re: Parameters in Report Builder (DAX query from Power BI Report)

niki_b — Fri, 02 Jul 2021 22:32:51 GMT

I already tried adding parameters from there. It gives me all the values for that parameter even after slicing.

As you can see in the above image, I have selected Increment in my parameter, it is still returning me all the rows. It means it is not working.

My DAX is as follows. I copied it from performance analyzer in my PowerBI report.

// DAX Query
DEFINE

VAR __DS0FilterTable =
TREATAS({"1"}, 'ruleBankTypesHistory'[Change?])

VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'ruleBankTypesHistory'[Type],
'ruleBankTypesHistory'[Old Value],
'ruleBankTypesHistory'[New Value],
'ruleBankTypesHistory'[Field],
'users'[Updated By],
'ruleBankTypesHistory'[updatedAt],
'Rules'[Rule Name],
'ruleset'[Ruleset],
__DS0FilterTable,
"CountRowsruleBankTypesHistory", CALCULATE(COUNTROWS('ruleBankTypesHistory'))
)
),
OR(
OR(
OR(
OR(
OR(
OR(
OR(
NOT(ISBLANK('ruleBankTypesHistory'[Type])),
NOT(ISBLANK('ruleBankTypesHistory'[Old Value]))
),
NOT(ISBLANK('ruleBankTypesHistory'[New Value]))
),
NOT(ISBLANK('ruleBankTypesHistory'[Field]))
),
NOT(ISBLANK('users'[Updated By]))
),
NOT(ISBLANK('ruleBankTypesHistory'[updatedAt]))
),
NOT(ISBLANK('Rules'[Rule Name]))
),
NOT(ISBLANK('ruleset'[Ruleset]))
)
)
),
"'ruleBankTypesHistory'[Type]", 'ruleBankTypesHistory'[Type],
"'ruleBankTypesHistory'[Old Value]", 'ruleBankTypesHistory'[Old Value],
"'ruleBankTypesHistory'[New Value]", 'ruleBankTypesHistory'[New Value],
"'ruleBankTypesHistory'[Field]", 'ruleBankTypesHistory'[Field],
"'users'[Updated By]", 'users'[Updated By],
"'ruleBankTypesHistory'[updatedAt]", 'ruleBankTypesHistory'[updatedAt],
"'Rules'[Rule Name]", 'Rules'[Rule Name],
"'ruleset'[Ruleset]", 'ruleset'[Ruleset]
)

Re: Parameters in Report Builder (DAX query from Power BI Report)

Icey — Tue, 06 Jul 2021 10:16:13 GMT

Hi niki_b ,

I create a simple sample for adding parameters. Please check:

Sample data:

USE Test CREATE TABLE Sales ( Product VARCHAR(100), Store VARCHAR(100), Sales INT ) INSERT INTO Sales VALUES('P1','S1',1) INSERT INTO Sales VALUES('P2','S1',2) INSERT INTO Sales VALUES('P3','S1',3) INSERT INTO Sales VALUES('P1','S2',4) INSERT INTO Sales VALUES('P4','S2',5)

Product	Store	Sales
P1	S1	1
P2	S1	2
P3	S1	3
P1	S2	4
P4	S2	5

1. Create a data source.

2. Create a dataset needed.

3. Create Parameter tables for the fields needed.

4. Create parameters.

5. Modify Query of the dataset created in step2.

6. Test.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.