MDX Expression in Excel Power pivot using = operator

Question

I am new to MDX, trying to create report in excel(Power Pivot OLAP) using tabular model cube.

I need convert below DAX expression into MDX expression,

DAX Experssion

  CALCULATE(
   sum('Finance GL'[Value in Origin Currency]),
       'Measure Name'[Measure Name] = "GSR", 
        filter('Customer',Customer[Customer PLTO Level 4 Name]="P3-TH KEY 
         ACCOUNTS"  ||
            Customer[Customer PLTO Code]="339883" ||
            Customer[Customer PLTO Code]="339801" ||
            Customer[Customer PLTO Code]="339879" || 
            Customer[Customer PLTO Code]="339899" ||
            Customer[Customer PLTO Code]="339830" ||
            Customer[Customer PLTO Code]="339878" || 
            Customer[Customer PLTO Code]="339928" || 
            Customer[Customer PLTO Code]="339929" ))

For MDX Equivalent Dimensions and attribute to use from cube are as follows,

    [Measure Name].[Measure Name]=[Measure Name].[Measure Name].&[GSR],

    [Customer].[Customer PLTO Level 4 Name]=[Customer].[Customer PLTO Level 
           4 Name].&[P3-TH KEY ACCOUNTS]

    [Customer].[Customer PLTO Code].

    [Customer].[Customer PLTO Code].&[339883], 
    [Customer].[Customer PLTO Code].&[339801],
    [Customer].[Customer PLTO Code].&[339879],
    [Customer].[Customer PLTO Code].&[339899],
    [Customer].[Customer PLTO Code].&[339830],
    [Customer].[Customer PLTO Code].&[339878],
    [Customer].[Customer PLTO Code].&[339928],
    [Customer].[Customer PLTO Code].&[339929]
    I have tried to use OR operator in MDX

My approach in MDX :

     sum(
      {
        (  
         [Measure Name].[Measure Name].&[GSR],
         [Customer].[Customer PLTO Level 4 Name].&[P3-TH KEY ACCOUNTS],
         strtomember("[Customer].[Customer PLTO Code].&[339883]") or 
         strtomember("[Customer].[Customer PLTO Code].&[339899]")


       )
      },
      [Measures].[Total Value in Origin Currency]
   )

but it is throwing Infinite recursion detected as an error message

Please advise me, any help is much appreciated.

Thanks,

Regards, Kedarnath

MoazRub MoazRub · Accepted Answer · 2018-11-24T22:20:56

If my understanding is correct you want the sum of "[Measures].[Total Value in Origin Currency]" when 1)[Measure Name].[Measure Name].&[GSR] and [Customer].[Customer PLTO Level 4 Name].&[P3-TH KEY ACCOUNTS] and any Customer PLTO Code 2)[Measure Name].[Measure Name].&[GSR] and the Customer PLTO Code is from the list provide and any Customer PLTO Level 4 Name. In MDX this would transalte to two tuples of a set. And both the tuples should have members from same arribute.

Based on this the query should be as below

Sum(
{
([Measure Name].[Measure Name].&[GSR],
[Customer].[Customer PLTO Level 4 Name].&[P3-TH KEY ACCOUNTS],
[Customer].[Customer PLTO Code].[All]),

([Measure Name].[Measure Name].&[GSR],
[Customer].[Customer PLTO Level 4 Name].[All],
{[Customer].[Customer PLTO Code].&[339883],
[Customer].[Customer PLTO Code].&[339801],
[Customer].[Customer PLTO Code].&[339879],
[Customer].[Customer PLTO Code].&[339899],
[Customer].[Customer PLTO Code].&[339830],
[Customer].[Customer PLTO Code].&[339878],
[Customer].[Customer PLTO Code].&[339928],
[Customer].[Customer PLTO Code].&[339929]}
)
},
[Measures].[Total Value in Origin Currency]
)

MDX Expression in Excel Power pivot using = operator

1 Answers