cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
loicr
Regular 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
Regular 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

3 REPLIES 3
loicr
Regular 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

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.

loicr
Regular 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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors