SSAS: Get a distinct Count based on two different elements
I need to create a distinct count of people who fall into two different dimensions.
One is called [Student Research Degree].[Is Research Degree Current].&[Yes]
The other is called [Student Research Degree].[Is Research Degree Complete].&[Yes]
If one or the other are Yes, or both, then I need to count the record.
If both are no, I can exclude it. I have a row counter measure called [Measures].[Student ID Distinct Count Hidden] already in place.
If I use just one element with the measure, I get the right answer, but if I try to cross join the other elements, I get a result of NULL.
eg
AGGREGATE(CROSSJOIN(
[Student Research Degree].[Is Research Degree Current].&[Yes]
,[Student Research Degree].[Is Research Degree Complete].&[Yes]
), [Measures].[Student ID Distinct Count Hidden])
I am aware that I can just land an extra value in the ETL, and have SQL do the work, and in the end this might be the solution. Is there a way of doing an OR statement on this sort of thing?
sql ssas mdx cube
asked Nov 12 at 1:52
Peppermallow
387
add a comment |
I need to create a distinct count of people who fall into two different dimensions.
One is called [Student Research Degree].[Is Research Degree Current].&[Yes]
The other is called [Student Research Degree].[Is Research Degree Complete].&[Yes]
If one or the other are Yes, or both, then I need to count the record.
If both are no, I can exclude it. I have a row counter measure called [Measures].[Student ID Distinct Count Hidden] already in place.
If I use just one element with the measure, I get the right answer, but if I try to cross join the other elements, I get a result of NULL.
eg
AGGREGATE(CROSSJOIN(
[Student Research Degree].[Is Research Degree Current].&[Yes]
,[Student Research Degree].[Is Research Degree Complete].&[Yes]
), [Measures].[Student ID Distinct Count Hidden])
I am aware that I can just land an extra value in the ETL, and have SQL do the work, and in the end this might be the solution. Is there a way of doing an OR statement on this sort of thing?
sql ssas mdx cube
asked Nov 12 at 1:52
Peppermallow
387
Distinct count is a very problematic measure. I would strongly suggest doing it via the SQL since the measure that you've got in place won't help you. Look here: blog.tallan.com/2017/02/09/…
– Hila DG
Nov 12 at 2:24
Does this code work for you ?AGGREGATE( {[Student Research Degree].[Is Research Degree Current].&[Yes] ,[Student Research Degree].[Is Research Degree Complete].&[Yes]} , [Measures].[Student ID Distinct Count Hidden])
– NickNick
Nov 12 at 10:29
add a comment |
I need to create a distinct count of people who fall into two different dimensions.
One is called [Student Research Degree].[Is Research Degree Current].&[Yes]
The other is called [Student Research Degree].[Is Research Degree Complete].&[Yes]
If one or the other are Yes, or both, then I need to count the record.
If both are no, I can exclude it. I have a row counter measure called [Measures].[Student ID Distinct Count Hidden] already in place.
If I use just one element with the measure, I get the right answer, but if I try to cross join the other elements, I get a result of NULL.
eg
AGGREGATE(CROSSJOIN(
[Student Research Degree].[Is Research Degree Current].&[Yes]
,[Student Research Degree].[Is Research Degree Complete].&[Yes]
), [Measures].[Student ID Distinct Count Hidden])
I am aware that I can just land an extra value in the ETL, and have SQL do the work, and in the end this might be the solution. Is there a way of doing an OR statement on this sort of thing?
sql ssas mdx cube
asked Nov 12 at 1:52
Peppermallow
387
I need to create a distinct count of people who fall into two different dimensions.
One is called [Student Research Degree].[Is Research Degree Current].&[Yes]
The other is called [Student Research Degree].[Is Research Degree Complete].&[Yes]
If one or the other are Yes, or both, then I need to count the record.
If both are no, I can exclude it. I have a row counter measure called [Measures].[Student ID Distinct Count Hidden] already in place.
If I use just one element with the measure, I get the right answer, but if I try to cross join the other elements, I get a result of NULL.
eg
AGGREGATE(CROSSJOIN(
[Student Research Degree].[Is Research Degree Current].&[Yes]
,[Student Research Degree].[Is Research Degree Complete].&[Yes]
), [Measures].[Student ID Distinct Count Hidden])
I am aware that I can just land an extra value in the ETL, and have SQL do the work, and in the end this might be the solution. Is there a way of doing an OR statement on this sort of thing?
sql ssas mdx cube
sql ssas mdx cube
asked Nov 12 at 1:52
Peppermallow
387
asked Nov 12 at 1:52
Peppermallow
387
asked Nov 12 at 1:52
Peppermallow
387
asked Nov 12 at 1:52
Peppermallow
387
asked Nov 12 at 1:52
Peppermallow
387
387
Distinct count is a very problematic measure. I would strongly suggest doing it via the SQL since the measure that you've got in place won't help you. Look here: blog.tallan.com/2017/02/09/…
– Hila DG
Nov 12 at 2:24
Does this code work for you ?AGGREGATE( {[Student Research Degree].[Is Research Degree Current].&[Yes] ,[Student Research Degree].[Is Research Degree Complete].&[Yes]} , [Measures].[Student ID Distinct Count Hidden])
– NickNick
Nov 12 at 10:29
add a comment |
Distinct count is a very problematic measure. I would strongly suggest doing it via the SQL since the measure that you've got in place won't help you. Look here: blog.tallan.com/2017/02/09/…
– Hila DG
Nov 12 at 2:24
Does this code work for you ?AGGREGATE( {[Student Research Degree].[Is Research Degree Current].&[Yes] ,[Student Research Degree].[Is Research Degree Complete].&[Yes]} , [Measures].[Student ID Distinct Count Hidden])
– NickNick
Nov 12 at 10:29
Distinct count is a very problematic measure. I would strongly suggest doing it via the SQL since the measure that you've got in place won't help you. Look here: blog.tallan.com/2017/02/09/…
– Hila DG
Nov 12 at 2:24
Distinct count is a very problematic measure. I would strongly suggest doing it via the SQL since the measure that you've got in place won't help you. Look here: blog.tallan.com/2017/02/09/…
– Hila DG
Nov 12 at 2:24
Does this code work for you ?
AGGREGATE( {[Student Research Degree].[Is Research Degree Current].&[Yes] ,[Student Research Degree].[Is Research Degree Complete].&[Yes]} , [Measures].[Student ID Distinct Count Hidden])– NickNick
Nov 12 at 10:29
Does this code work for you ?
AGGREGATE( {[Student Research Degree].[Is Research Degree Current].&[Yes] ,[Student Research Degree].[Is Research Degree Complete].&[Yes]} , [Measures].[Student ID Distinct Count Hidden])– NickNick
Nov 12 at 10:29
add a comment |
1 Answer
1
active
oldest
votes
No, the TUPLE of &[YES], &[YES] doesn't create an OR situation, where I want [NO]s when the other is yes.
I started looking at a subtractive approach where I started with the ALL set, and removed the distinct count of invalid combinations in a tuple and subtracted that from the grand total. This approach did work, but ONLY because the data allowed for it. If a person could have been in multiple combinations, this wouldn't have worked.
I'm currently testing that approach with the rest of the cube. By all appearances this works perfectly, but I will go with ETL if any bugs or mismatches can be proven.
answered Nov 12 at 20:46
Peppermallow
387
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%2f53255076%2fssas-get-a-distinct-count-based-on-two-different-elements%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
No, the TUPLE of &[YES], &[YES] doesn't create an OR situation, where I want [NO]s when the other is yes.
I started looking at a subtractive approach where I started with the ALL set, and removed the distinct count of invalid combinations in a tuple and subtracted that from the grand total. This approach did work, but ONLY because the data allowed for it. If a person could have been in multiple combinations, this wouldn't have worked.
I'm currently testing that approach with the rest of the cube. By all appearances this works perfectly, but I will go with ETL if any bugs or mismatches can be proven.
answered Nov 12 at 20:46
Peppermallow
387
add a comment |
No, the TUPLE of &[YES], &[YES] doesn't create an OR situation, where I want [NO]s when the other is yes.
I started looking at a subtractive approach where I started with the ALL set, and removed the distinct count of invalid combinations in a tuple and subtracted that from the grand total. This approach did work, but ONLY because the data allowed for it. If a person could have been in multiple combinations, this wouldn't have worked.
I'm currently testing that approach with the rest of the cube. By all appearances this works perfectly, but I will go with ETL if any bugs or mismatches can be proven.
answered Nov 12 at 20:46
Peppermallow
387
add a comment |
No, the TUPLE of &[YES], &[YES] doesn't create an OR situation, where I want [NO]s when the other is yes.
I started looking at a subtractive approach where I started with the ALL set, and removed the distinct count of invalid combinations in a tuple and subtracted that from the grand total. This approach did work, but ONLY because the data allowed for it. If a person could have been in multiple combinations, this wouldn't have worked.
I'm currently testing that approach with the rest of the cube. By all appearances this works perfectly, but I will go with ETL if any bugs or mismatches can be proven.
answered Nov 12 at 20:46
Peppermallow
387
No, the TUPLE of &[YES], &[YES] doesn't create an OR situation, where I want [NO]s when the other is yes.
I started looking at a subtractive approach where I started with the ALL set, and removed the distinct count of invalid combinations in a tuple and subtracted that from the grand total. This approach did work, but ONLY because the data allowed for it. If a person could have been in multiple combinations, this wouldn't have worked.
I'm currently testing that approach with the rest of the cube. By all appearances this works perfectly, but I will go with ETL if any bugs or mismatches can be proven.
answered Nov 12 at 20:46
Peppermallow
387
answered Nov 12 at 20:46
Peppermallow
387
answered Nov 12 at 20:46
Peppermallow
387
answered Nov 12 at 20:46
Peppermallow
387
387
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53255076%2fssas-get-a-distinct-count-based-on-two-different-elements%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
Distinct count is a very problematic measure. I would strongly suggest doing it via the SQL since the measure that you've got in place won't help you. Look here: blog.tallan.com/2017/02/09/…
– Hila DG
Nov 12 at 2:24
Does this code work for you ?
AGGREGATE( {[Student Research Degree].[Is Research Degree Current].&[Yes] ,[Student Research Degree].[Is Research Degree Complete].&[Yes]} , [Measures].[Student ID Distinct Count Hidden])– NickNick
Nov 12 at 10:29