Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
OUP
Frequent Visitor

How to pass SAP Variables via MDX query

Hi all,

 

I am trying to pull a significant chunk of data from an extremely large SAP BW InfoCube. Currently I am pulling the data from SAP BW via the inbuilt Power BI connector and applying a filter at the MDX query stage which helps significantly reduce the size of the data pulled.

 

Example taken from Microsoft's MDX documentation:

 

SELECT  
    { [Measures].[Sales Amount],   
        [Measures].[Tax Amount] } ON 0,  
    { [Date].[Fiscal].[Fiscal Year].&[2002],   
        [Date].[Fiscal].[Fiscal Year].&[2003] } ON 1  
FROM [Adventure Works]  
WHERE ( [Sales Territory].[Southwest] )  

 


The caveat to this is that I am querying an InfoCube which requires a value to be passed through a parameter. I can do this when importing not via MDX:

OUP_0-1734472369544.png


How would I go about doing this via MDX query? The above process generates the Power Query M code below:

 

{Cube.ApplyParameter, "[0PERIV]", {"[0FISCVARNT].[O1]"}}

 

 

2 REPLIES 2
Anonymous
Not applicable

Hi @OUP ,

 

Please try this:

Define the parameter in your MDX query:

 WITH 
   MEMBER [Measures].[ParameterValue] AS '[0FISCVARNT].[O1]'
   SELECT 
       { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,
       { [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS
   FROM [Adventure Works]
   WHERE ( [Sales Territory].[Southwest], [Measures].[ParameterValue] )

Apply the parameter in Power Query M code:

let
       Source = Cube.ApplyParameter(
           Cube = SAPBW.Cube("YourServer", "YourCube"),
           ParameterName = "[0PERIV]",
           ParameterValue = {"[0FISCVARNT].[O1]"}
       ),
       FilteredData = Cube.Transform(Source, {
           {Cube.AddMeasureColumn, "Sales Amount", "[Measures].[Sales Amount]"},
           {Cube.AddMeasureColumn, "Tax Amount", "[Measures].[Tax Amount]"}
       })
   in
       FilteredData

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

OUP
Frequent Visitor

Thanks for your response @Anonymous.

 

I'm running into the following error and have tried both variations of Cube = and Cubes = :

OUP_0-1734657727293.png


Steps I followed:
1. Defined parameter via query -> error outlining that I need to specify parameter appears
2. Advanced editor; applied parameters (ignored FilteredData)

Expression.Error occurs.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors