Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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]"}}
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.
Thanks for your response @Anonymous.
I'm running into the following error and have tried both variations of Cube = and Cubes = :
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.