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

[loicr]

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.

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

Any idea to resolve this?

Thanks a lot in advance!

[Janaki_Sankaran]

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 · Accepted solution]

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 why the query does not return anything in Query designed: Detect missing parameters in DAX queries - SQLBI
  • To understand how multi-value parameters can work: Adding multiple parameters to a DAX query as a filter in Power BI Report Builder | by Janani Govinda...
• 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]

Hi @loicr ,

Please try to add paramters like so:

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.

[W_O_L_F]

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

[loicr]

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,