Forum Discussion

anandgovindaraj's avatar
anandgovindaraj
Frequent Visitor
8 years ago

Dynamic MDX query

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. 

Need Help

4 Replies

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago

    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's avatar
      anandgovindaraj
      Frequent Visitor
      8 years ago

      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. 

       

       

       

       

      • Seward12533's avatar
        Seward12533
        Solution Sage
        8 years ago
        Try searching for this
        “Passing-parameter-to-sql-from-dropdown-list-in-powerBI”

        Use # before the parameter name.