Passing parameters to MDX source queries

[keobrie]

I have read in several places that Power BI Parameters can be passed to an MDX query script but I cannot get it to work.

My script works well with SQL Server Reporting Services parameters.  The MDX parameter name is @varEndPeriod (MDX requires parameters to begin with the @ sign).

1. Should I name my Power BI parameter varEndPeriod or @varEndPeriod?

2. Do I need to make any special adjustments to my MDX code?  An excerpt from the MDX script is currently:

FROM (SELECT ({STRTOMEMBER(@varEndPeriod).lag(11) : STRTOMEMBER(@varEndPeriod)}) ON COLUMNS

3. Do I have to change the value of the Parameter in any way?  For example SSRS changes the format of the text that is passed from:

[dim_ReportingPeriods].[Period Start].&[2017-12-01T00:00:00]

to 

\[dim_ReportingPeriods\].\[Period Start\].&\[2017-12-01T00:00:00\]

4. Finally, can you confirm that MDX parameters can be passed from Power BI?

Any help would be appreciated.

Thx

[v-danhe-msft]

Hi @keobrie,

Based on my research, you could refer to below link to create parameters to pass the MDX parameters:

https://community.powerbi.com/t5/Desktop/Passing-parameters-to-MDX-source-queries/td-p/98255

Regards,

Daniel He

[keobrie · Accepted solution]

I was able to resolve this issue successfully.  When implemented in Power BI, the MDX query is stored as a string.  The trick turned out to be that the traditional SSRS/MDX parameter syntax using the @ sign in front of the parameter is not necessary and does not work.  In addition, the STRTOMEMBER function is not necessary.

Instead, the insertion of a parameter is merely a string concatenation.  Thus at the point where the parameter needs insertion, close the string with a quote ("), use the traditional string concatenation function (&) before and after the raw parameter name (in my case varEndPeriod not @varEndPeriod) and then insert another quote (") to begin the next section of the string.

I'm not sure if there is or will be a more advanced approach to parameter driven MDX queries in the future but for now this works.

In the meantime, the references in the Blogs to dynamic query creation with variables for servers, databases, and record sources now make perfect sense.

The key for porting parameter driven MDX queries to Power BI at this point in time is to understand that the query is nothing more than a string and the parameter is nothing more than a variable that can be concatenated into the string using the & for string concatenation.

So the correct syntax for the snippet I posted was:

FROM (SELECT ({"&varEndPeriod&".lag(11) : "&varEndPeriod&"}) ON COLUMNS

Note that this FROM statement is embedded in a more complex statement and the terminal ) is several lines below.