cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akwAUBI Frequent Visitor
Frequent Visitor

Passing parameters to MDX source queries

Hi all,

 

Having a hard time finding a successful example of evaluating parameters aside from TSQL, API calls, and M..

To set the stage, I've got an SSAS Multidimensional Cube model defined with a functional dynamic set that I'll call [YTD Months Set]. This set looks for a single selected month member from a Year - Quarter - Month style hierarchy and based on it, returns all YTD months. In effect, if I were to call an MDX query..

 

SELECT { [YTD Months Set] }

FROM ( SELECT ( { [Dynamic Set Month Selection].[Month].&[2016].&[4] } ) ON COLUMNS

FROM [CUBE] )

 

It would return a set of members:

 

Jan (2016)
Feb (2016)
Mar (2016)
Apr (2016)

 

My use case involves passing such an MDX query to Power BI to generate a YTD dataset, but I would like to enable end users to specify a Year and Month parameter which would ideally be applied to the query using a StrToMember function.

 

Something like...

FROM ( SELECT ( { StrToMember('[Dynamic Set Month Selection].[Month].&['  + YearParam + '].&[' + MonthParam + ']') } ) ON COLUMNS

 

Is it possible to configure this sort of parameter within the Power BI Query Editor > Manage Parameters interface and pass it to an in-line query definition in Power BI desktop?

 

I'm most interested in required settings on the parameter and any syntax requirements for the Query Refresh to detect and apply a defined Power BI parameter

 

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Passing parameters to MDX source queries

Hi @akwAUBI,

 

>>Is it possible to configure this sort of parameter within the Power BI Query Editor > Manage Parameters interface and pass it to an in-line query definition in Power BI desktop?

 

Yes,it is possible, you can take a look at below link:

Create a list then pass as parameters to TSQL

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Highlighted
akwAUBI Frequent Visitor
Frequent Visitor

Re: Passing parameters to MDX source queries

Thanks @v-shex-msft, it took me a moment to look past the TSQL vs MDX application in realization that the M script is really where the parameter interaction should be configured. For anyone else interested, I concluded the following from what Xiaoxin referenced:

"

function formula:

 

let
    loadDataBase=(ServerName as text,DataSource as text, parameter as text) as table=>
    let
        SQLQuery = "select * from test1121  where Quarter in ("& parameter &")",
        Source = Sql.Database("ServerName", "DataSource",[Query=SQLQuery])
    in
        Source
in
    loadDataBase

"

Per my initial post, I seemed to have been overly concerned with the MDX StrToMember function being at the core of successfully evaluating my parameter. the M syntax stores the query, be it MDX, TSQL or otherwise, as a single string which should be concatenated in the M context to slot-in a given parameter.
Two extra points I needed to resolve were:

  1. Parameter name needed to be a single word
  2. My parameter value was an integer (1-12 to represent months) so I needed to supply an M syntax int to string conversion as follows: & Number.ToText(ReportingMonth) &

     

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Passing parameters to MDX source queries

Hi @akwAUBI,

 

>>Is it possible to configure this sort of parameter within the Power BI Query Editor > Manage Parameters interface and pass it to an in-line query definition in Power BI desktop?

 

Yes,it is possible, you can take a look at below link:

Create a list then pass as parameters to TSQL

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Highlighted
akwAUBI Frequent Visitor
Frequent Visitor

Re: Passing parameters to MDX source queries

Thanks @v-shex-msft, it took me a moment to look past the TSQL vs MDX application in realization that the M script is really where the parameter interaction should be configured. For anyone else interested, I concluded the following from what Xiaoxin referenced:

"

function formula:

 

let
    loadDataBase=(ServerName as text,DataSource as text, parameter as text) as table=>
    let
        SQLQuery = "select * from test1121  where Quarter in ("& parameter &")",
        Source = Sql.Database("ServerName", "DataSource",[Query=SQLQuery])
    in
        Source
in
    loadDataBase

"

Per my initial post, I seemed to have been overly concerned with the MDX StrToMember function being at the core of successfully evaluating my parameter. the M syntax stores the query, be it MDX, TSQL or otherwise, as a single string which should be concatenated in the M context to slot-in a given parameter.
Two extra points I needed to resolve were:

  1. Parameter name needed to be a single word
  2. My parameter value was an integer (1-12 to represent months) so I needed to supply an M syntax int to string conversion as follows: & Number.ToText(ReportingMonth) &

     

View solution in original post

Smasroor Frequent Visitor
Frequent Visitor

Re: Passing parameters to MDX source queries

Hi @akwAUBI,

 

i am struggling with similar issue. I have a list of employee names in my parameter dropdown (populated using a query). I want to pass the parameter value selected by user to the main MDX query that pulls all the data points. Would you be able to plaease share step by step process with screenshots and syntex.

 

Thanks SM

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 308 members 3,558 guests
Please welcome our newest community members: