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

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 Govindasamy | BI3 Technologies | Medium
• To understand how to change the default values: https://www.mssqltips.com/sqlservertip/3506/set-select-all-as-default-for-multivalue-report-parameters-in-sql-server-reporting-services/ 

  Hope it will help someone!

  Best,