cancel
Showing results for 
Search instead for 
Did you mean: 

Power Query not load data from SSAS cube using MDX script

Hi Team,

 

Power Query (in both Power BI and Excel) not load data from SSAS cube using MDX script. It happens when you mention empty set on columns.

 

Steps to receive such behavior:

1. Open Power BI Desktop and Press Get Data from SSAS.

2. Choose Import mode and enter MDX script.

 

Sample script which doesn't work:

SELECT 
     {} ON 0,
     [Sales Territory].[Sales Territory Region].Children ON 1
FROM
     [Adventure Works]

And the one which works:

SELECT 
	[Measures].[Internet Sales Amount] ON 0,
	[Sales Territory].[Sales Territory Region].Children ON 1
FROM
	[Adventure Works]

But both queries return a result in SSMS tool.

 

Regards,
Ruslan

 

 

Status: New
Comments
Moderator

Hi @zoloturu,

 

I have consulted this issue internally, will update here once I get any information. 

 

Best Regards,
Qiuyun Yu 

Moderator

Hi @zoloturu,

 

I confirmed from PG team that Power BI always returned no results for empty measures.

 

Best Regards,
Qiuyun Yu 

Advisor

Hi @v-qiuyu-msft,

 

Could you provide some more information about this unexpected behavior?

 

Thanks

 

Regards,
Ruslan

Moderator

Hi @zoloturu ,

 

This is an expected behavior. As I posted in previous reply, as empty measure is used in the query, SSMS returns a column  for dimension column values, but for Power BI, it always returns no result means blank dataset. 

 

Best Regards,
Qiuyun Yu