Dynamic MDX query

[anandgovindaraj]

Hi! 

I've got an SSAS Multidimensional Cube and I use the following MDX query to get data for a specific study portoflio - 18563.  

SELECT NON EMPTY { [Measures].[Mean] } ON COLUMNS { ([Item Type].
[Variables].[Event Dates].[Date Intervals].[Date Intervals]) } ON ROWS FROM ( SELECT ( { [Iteration Items]] } )
WHERE ([Study Portfolios].&[18563]&[1019026] )

Is it possbile to make this query dynamic where the user gets to pick from a list of study numbers ( lets says a slicer selection) and the query gets evaluated ? 

Thanks. 

[Seward12533]

Since PowerQuery is run independently of any values the user may pick it does not do what you want out of the box. I saw a similar dicussion on this a few days ago and the solution was to use "R" I could not find this one but check this out this post 

I let powerquery pull in the query and filter there and then use DAX and filter context.  PowerBI is quite efficient and I do this with tables of 100s of thousands of records with no issues. 

I also saw some tutorials on Guy In Cube here is one - https://www.youtube.com/watch?v=iiNDq2VrZPY but this had the drawback of using a speadsheet to define the filter (selected values) and powerquery pulled in that spreadsheet to filter the results. 

[anandgovindaraj]

Thank you for your comments. 

The link to page where using R is a proposed solution is not working. Could you please repost the link ? 

Ideally, if Live Connection to Multi-dimensional Cube functionality is not limited (can't create measures etc)  I would be using that. Hopefully, live connections becomes more flexible.  

I think i might follow the tutorials by Guy in a Cube that you shared. Seems to be the closest solution to what I'm trying to do.  Although I'm running into 'string to numeric' conversion issues in the MDX query.  I created a new parameter 'StudyID' as shown below in the image. 

However, Im getting the following error when i replace the value 18563 in my original query to '&StudyID'. What will be the correct way to reference studyid parameter in the M query. 

 

[Anonymous]

Bit late to the party but thought I'd join in! 🙂

The syntax that works for me is...

"&StudyID&"

 The trick to editing an MDX is to not try and edit the Query string from within the Analysis Services call. Instead, create a Variable in M Code to hold your Query as a string, then you can edit this before its passed to the Analysis Services call.

It is also possible to use MDX queries to create lists that can then act as selections for your variables. I am not sure a visualisation could control the data pull though, I think you would need the user to understand parameters in Power BI.

[Seward12533]

Try searching for this
“Passing-parameter-to-sql-from-dropdown-list-in-powerBI”

Use # before the parameter name.