How does passing a parameter to MDX works?

[reneincer]

Hi community!

I am using a SSAS model with Import option and MDX queries, I have read many posts about passing parameters to queries, and all those posts are linked to this:

http://community.powerbi.com/t5/Desktop/Create-a-list-then-pass-as-parameters-to-TSQL/m-p/97761#M411...

Is there a new way to do this? I think it would be great if someone only selects a column and make it parameter and this parameter is auto embedded in source query.

I know you can select a column in the edit queries sections and assign it a parameter, but what I dont know is, does PowerBI extracts all data and then apply the parameter? or, does it pass the parameter before execution? If it is the later, thats the reason I need to pass the parameter directly in the query.

My query is very simple, Why passing a parameter is so complicated?

SELECT 
 NON EMPTY { [Measures].[Measure1], [Measures].[Measure2] } ON COLUMNS, 
 NON EMPTY {
 [GroupA].[Level].[Territory].ALLMEMBERS * 
 [GorupB].[Level].[SalesRegion].ALLMEMBERS } ON ROWS
FROM [Cube]
WHERE ( [ProcessDate].[Date].&[2016-12-31T00:00:00] ) 

Suppose I want to pass process date, how could I achieve this? I dont want to import all the table because my RAM crash, it is a very big cube (periodic snapshot),

Thanks

[reneincer]

Hi community!

I am using a SSAS model with Import option and MDX queries,

I have read many posts about passing parameters to queries, and all those posts are linked to this topic (passing to SQL): http://community.powerbi.com/t5/Desktop/Create-a-list-then-pass-as-parameters-to-TSQL/m-p/97761#M411...

Is there a new way to do this? I think it would be great if someone only selects a column and make it parameter and this parameter is auto embedded in source query.

I know you can select a column in the edit queries sections and assign it a parameter, but what I dont know is, does PowerBI extracts all data and then apply the parameter? or, does it pass the parameter before execution? If it is the later, thats the reason I need to pass the parameter directly in the query.

My query is very simple, Why passing a parameter is so complicated?

SELECT 
 NON EMPTY { [Measures].[Measure1], [Measures].[Measure2] } ON COLUMNS, 
 NON EMPTY {
 [GroupA].[Level].[Territory].ALLMEMBERS * 
 [GorupB].[Level].[SalesRegion].ALLMEMBERS } ON ROWS
FROM [Cube]
WHERE ( [ProcessDate].[Date].&[2016-12-31T00:00:00] ) 

Suppose I want to pass process date, how could I achieve this?

Thanks

[v-sihou-msft · Accepted solution]

@reneincer

In Power BI, to use parameter, you can only generate a list with existing column and use it in your source query, like you already found. There's no other way.

And Power BI Query Editor extract all data first. After that if you apply parameter, it will re-generate the query with parameter selection as condition. It's unlike the parameters in Reporting Services. The Query Editor in SSRS will generate a hidden dataset for each parameter used in MDX.

In your scenario, I suggest you import the underlying dimension table into Power BI. Create a List with that date key column and use it to concatenate into a date member in your MDX.

Regards,