Power Query not load data from SSAS cube using MDX script

[zoloturu]

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

[v-qiuyu-msft]

Hi @zoloturu,

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

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @zoloturu,

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

Best Regards,
Qiuyun Yu 

[zoloturu]

Hi @v-qiuyu-msft,

Could you provide some more information about this unexpected behavior?

Thanks

Regards,
Ruslan

[v-qiuyu-msft]

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