MDX-How to return multiple measures

Question

I'm trying to optimize a 2M row SSAS query into Power BI by using MDX prior to the Power Query. I have experience in T-SQL and found a website to help translate T-SQL experience into MDX, which was successful for some queries (basic rows/column selects, crossjoins, non empty, order by, filter, where). So now I want to get in my sales data which contains three dimensions and four measures but I get the following error:

Executing the query ... Query (3, 1) The 'Measures' hierarchy appears more than once in the tuple. Run complete

I attempted a few variations related to crossjoining the measures and the dimensions, only selecting one measure (which still took too long), and specifying members vs children.

'''

 select
    ([Date].[OrderDate].children, [Customer].[CustID].children, [ProdLevel].[ProdNumber].children) on rows,
    ([Measures].[Revenue], [Measures].[Units], [Measures].[ASP], [Measures].[Profit]) on columns
    from [RepProdDB]
    where [ProdLevel].[Prod Description].[MyBusinessUnit]

'''

Looking up the error: "The 'Measures' hierarchy appears more than once in the tuple." is a bit vague to me as I have slight but probably incomplete understanding of tuples.

My hope is to have something that I can easily get in PivotTable OLAP, Power Pivot, and Power Query but using the actual MDX code. Thoughts?

MoazRub MoazRub · Accepted Answer · 2019-07-06T18:32:39

So you need to understand the diffrence between tuples and sets.

select
non empty
(
[Date].[OrderDate].children, 
[Customer].[CustID].children, 
[ProdLevel].[ProdNumber].children
) 
on rows,
{
[Measures].[Revenue], 
[Measures].[Units], 
[Measures].[ASP], 
[Measures].[Profit]
} 
on columns
from [RepProdDB]
where 
[ProdLevel].[Prod Description].[MyBusinessUnit]

MDX-How to return multiple measures

1 Answers