MDX Expression in Excel Power pivot using = operator
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
excel mdx powerpivot
add a comment |
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
excel mdx powerpivot
add a comment |
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
excel mdx powerpivot
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
excel mdx powerpivot
excel mdx powerpivot
asked Nov 23 '18 at 7:25
kedarnathkedarnath
62
62
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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]
)
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53442293%2fmdx-expression-in-excel-power-pivot-using-operator%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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]
)
add a comment |
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]
)
add a comment |
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]
)
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]
)
answered Nov 24 '18 at 22:20
MoazRubMoazRub
9541413
9541413
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53442293%2fmdx-expression-in-excel-power-pivot-using-operator%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown