Forum Discussion

akwAUBI's avatar
akwAUBI
Regular Visitor
9 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

  • akwAUBI's avatar
    akwAUBI
    9 years ago

    Thanks Anonymous, 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) &

       

4 Replies

  • aritzfb's avatar
    aritzfb
    New Member
    6 years ago

    Hello,

     
    I'm not sure that the next workaround will be fine for your requirements. When we need to extract some data from SSAS Cubes in PowerBI (PowerQuery) by using parameters we proceed by this way:
     
    We create an excel file in order to insert the filters values. For instance if you want to filter some years (2019 and 2020) we create a table into excel that contains that values
    We create a query in PowerQuery that extract that values from excel
    let
        Origen = Excel.Workbook(File.Contents("C:\temp\Filters.xlsx"), null, true),
        Tabla2_Table = Origen{[Item="Tabla2",Kind="Table"]}[Data],
        #"Tipo cambiado" = Table.TransformColumnTypes(Tabla2_Table,{{"Year Filter", type text}})
    in
        #"Tipo cambiado"
     
    We connect to SSAS cube and extract all the years of the cube using the PowerQuery cube navigator, not writing an MDX . After that, in the same query, we create a custom column that contains the unique name of the years members using  Cube.AttributeMemberId() formula. Then, we combine this query with the previous query by the year fields in order to filter the correct members. We delete all columns in this query except the unique names columns and we convert this query into list
    let
        Origen = AnalysisServices.Database("yourSSASserver", "yourSSASdatabase", [TypedMeasureColumns=true, Implementation="2.0"]),
        cube1 = Origen{[Id="yourCUBE"]}[Data],
        cube2 = cube1{[Id="yourCUBE"]}[Data],
        #"Elementos agregados" = Cube.Transform(cube2,
            {
                {Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Calendar Year].[Calendar Year]"}, {"Calendar Year"}}
            }),
        #"Personalizada agregada" = Table.AddColumn(#"Elementos agregados", "unm", each Cube.AttributeMemberId([#"Calendar Year"])),
        #"Consultas combinadas" = Table.NestedJoin(#"Personalizada agregada", {"Calendar Year"}, #"Excel File", {"Year Filter"}, "Excel File", JoinKind.Inner),
        #"Columnas quitadas" = Table.RemoveColumns(#"Consultas combinadas",{"Calendar Year", "Excel File"}),
        unm = #"Columnas quitadas"[unm]
    in
        unm
    We create another query to the SSAS cube extracting the year attribute (without MDX/using navigator), and filter them using the List.Contains() formula with the list created in the previous step. After that you have been able to continuing adding the measures and attributes you need from the cube.
    let
        Origen = AnalysisServices.Database("yourSSASserver", "yourSSASdatabase", [TypedMeasureColumns=true, Implementation="2.0"]),
        cube1 = Origen{[Id="yourCUBE"]}[Data],
        cube2 = cube1{[Id="yourCUBE"]}[Data],
        #"Elementos agregados" = Cube.Transform(cube2,
            {
                {Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Calendar Year].[Calendar Year]"}, {"Calendar Year"}}
            }),
        #"Filas filtradas" = Table.SelectRows(#"Elementos agregados", each (List.Contains( #"Cube Filter", Cube.AttributeMemberId([#"Calendar Year"]) )) )
    in
        #"Filas filtradas"
     
     
    Then you could change the excel values before you will refresh your queries and the ssas data will be filtered by that values.
     
     
    I have special thanks to Sergio Alvaro Panizo who has helped me find this solution.
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • akwAUBI's avatar
      akwAUBI
      Regular Visitor
      9 years ago

      Thanks Anonymous, 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) &

         

      • Smasroor's avatar
        Smasroor
        Frequent Visitor
        8 years ago

        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