Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
5 | |
4 | |
2 | |
2 |
User | Count |
---|---|
13 | |
9 | |
7 | |
2 | |
2 |