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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
loicr
Frequent Visitor

Add parameter in query designer (query from Power BI Performance analyser)

Hi guys,

 

I am trying to add a parameter in my report and add the parameter in the query designer module.

The query was generated within Power BI via Performance analyser.

I saw different posts on this issue but no clear solution.

First I have tried to create a parameter in the report (in my dataset properties) and add it with @ in my query such as

 

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({@Test}, 'FACT - Conflicts'[Conflict category])

However, I have an error message that the parameter @test was not declared.

So I declared it again through "Query Parameters" module within the query designer (same name and another name).

This time I can execute the query but it returns no row with a default value "Group" (which should not be the case - if I had put TREAT({"Group"}, it returns some rows).

 

I tried to see what were the values in the Parameters properties window and I can see this.

loicr_0-1638479046799.png

Which I don't understand because "Test" is not the name of my dataset..

 

Any idea to resolve this?

Thanks a lot in advance!

 

1 ACCEPTED SOLUTION
loicr
Frequent Visitor

Hi all,

 

I was able to find a solution to my problem (that raised other problems which I solved as well).

What I was trying to do:

- Use a DAX query (created by Power Bi Desktop) into Power BI Report Builder but modify it to insert some parameters that I could use in my report in Power BI Report Builder.

- The is the different steps I have done (for a multi-value parameter):

  • Add the query in Query Designer. My query from Power BI Desktop looked like this (ensure to use a slicer with at least one value unchecked. If only one item is selected, this is what you DAX query should start with. And this is what we are going to modify.

     // DAX Query DEFINE VAR __DS0FilterTable = TREATAS({"Entity"}, 'DIM_Legal_Entities'[Legal_Entity]) 

     

     

  • Add the parameter in the query designer by clicking on "Query Parameters";
  • Integrate the parameter in the DAX query like this (in my case my parameter is called "Legal_Entities")

     VAR __DS0FilterTable = CALCULATETABLE('FACT_Conflicts',PATHCONTAINS(@Legal_Entities,DIM_Legal_Entities[Legal_Entity])) 

     

     

  • Click on okay - don't try to execute the query; even if you select Default values in Query parameters, the query won't return any row (I'm not sure why at the moment)
  • The parameter should be created automatically
  • Right-Click on the dataset (on which you execute the query) then Dataset properties / Parameters
  • Set the following expression for the parameter; then click on okay: =join(Parameters!Legal_Entities.Value,"|")
  • Now, we need to change the available values and default values - for some reasons as well, declaring them in Query parameters does not work as expected (or maybe I do something wrong).
  • Right-Click on your parameter (Parameter properties) in the parameters section of the main left menu:
  • Click on available values and change the different fields (in my case, I'm using a different value that the one on which I execute the query - the query is executed on a FACT dataset; where the parameter is linked to a DIM dataset)

    These are the articles that helped me to understand:

  • To understand how to change the default values: https://www.mssqltips.com/sqlservertip/3506/set-select-all-as-default-for-multivalue-report-paramete...

    Hope it will help someone!

    Best,

View solution in original post

5 REPLIES 5
Janaki_Sankaran
Regular Visitor

Hi @loicr ,
I need your help in this similar use case which I'm currently working on. I'm not clear on how to make the parameters work. Should I need to add Report Parameters first and then create a Dataset where in the Query Designer do I need to add the query parameters separately? If so, how should I associate the Report and Query parameters? Please assist here.

 

loicr
Frequent Visitor

Hi all,

 

I was able to find a solution to my problem (that raised other problems which I solved as well).

What I was trying to do:

- Use a DAX query (created by Power Bi Desktop) into Power BI Report Builder but modify it to insert some parameters that I could use in my report in Power BI Report Builder.

- The is the different steps I have done (for a multi-value parameter):

  • Add the query in Query Designer. My query from Power BI Desktop looked like this (ensure to use a slicer with at least one value unchecked. If only one item is selected, this is what you DAX query should start with. And this is what we are going to modify.

     // DAX Query DEFINE VAR __DS0FilterTable = TREATAS({"Entity"}, 'DIM_Legal_Entities'[Legal_Entity]) 

     

     

  • Add the parameter in the query designer by clicking on "Query Parameters";
  • Integrate the parameter in the DAX query like this (in my case my parameter is called "Legal_Entities")

     VAR __DS0FilterTable = CALCULATETABLE('FACT_Conflicts',PATHCONTAINS(@Legal_Entities,DIM_Legal_Entities[Legal_Entity])) 

     

     

  • Click on okay - don't try to execute the query; even if you select Default values in Query parameters, the query won't return any row (I'm not sure why at the moment)
  • The parameter should be created automatically
  • Right-Click on the dataset (on which you execute the query) then Dataset properties / Parameters
  • Set the following expression for the parameter; then click on okay: =join(Parameters!Legal_Entities.Value,"|")
  • Now, we need to change the available values and default values - for some reasons as well, declaring them in Query parameters does not work as expected (or maybe I do something wrong).
  • Right-Click on your parameter (Parameter properties) in the parameters section of the main left menu:
  • Click on available values and change the different fields (in my case, I'm using a different value that the one on which I execute the query - the query is executed on a FACT dataset; where the parameter is linked to a DIM dataset)

    These are the articles that helped me to understand:

  • To understand how to change the default values: https://www.mssqltips.com/sqlservertip/3506/set-select-all-as-default-for-multivalue-report-paramete...

    Hope it will help someone!

    Best,

Icey
Community Support
Community Support

Hi @loicr ,

 

Please try to add paramters like so:

parameter1.gif

parameter2.gif

 

Reference: 

Tutorial: Add a Parameter to Your Report (Report Builder) - SQL Server Reporting Services (SSRS) | M...

 

 

 

Best Regards,

Icey

 

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

Great post thanks! Just wanted to comment on @param if connecting to Oracle it is :Param 

loicr
Frequent Visitor

Hi @Icey ,

 

Thanks for your input, however, this does not solve my issue.

As explained, I know how to add a parameter and did it several times.

The example you displayed relates to SQL where I want to add my parameter in a DAX query (copied from Power BI).

Best,

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors