PowerBI IF value IN TOPN values then TRUE
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm sure I'm missing something simple here. But I'm having no luck.
What I'd like to do is to create a new TRUE/FALSE column based upon a value within a column being in the top 2 results of the same column.
This snippet currently works for the "Max Value"
isCurrent =
IF (
'Program'[MajorVersion] = MAXX ( 'Program', 'Program'[MajorVersion] ),
TRUE (),
FALSE ()
)
What I'd prefer working is not just the MAXX value but the top 2 values within that column. So I am using the TOPN function below. Unfortunately, this is not working.
isCurrent =
IF (
'Program'[MajorVersion]
IN TOPN (
2,
FILTER ( 'Program', 'Program'[MajorVersion] ),
'Program'[MajorVersion], DESC
),
TRUE (),
FALSE ()
)
Table example:
ID MajorVersion
1 18.4
2 18.4
3 18.5
4 18.2
5 18.6
6 18.6
7 18.4
8 18.4
9 18.6
10 18.6
Desired Result: (Since the top 2 values within MajorVersion would be 18.5 & 18.6)
ID MajorVersion isCurrent
1 18.4 FALSE
2 18.4 FALSE
3 18.5 TRUE
4 18.2 FALSE
5 18.6 TRUE
6 18.6 TRUE
7 18.4 FALSE
8 18.4 FALSE
9 18.6 TRUE
10 18.6 TRUE
powerbi dax
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
asked Nov 16 '18 at 19:30
user3893312user3893312
104
add a comment |
I'm sure I'm missing something simple here. But I'm having no luck.
What I'd like to do is to create a new TRUE/FALSE column based upon a value within a column being in the top 2 results of the same column.
This snippet currently works for the "Max Value"
isCurrent =
IF (
'Program'[MajorVersion] = MAXX ( 'Program', 'Program'[MajorVersion] ),
TRUE (),
FALSE ()
)
What I'd prefer working is not just the MAXX value but the top 2 values within that column. So I am using the TOPN function below. Unfortunately, this is not working.
isCurrent =
IF (
'Program'[MajorVersion]
IN TOPN (
2,
FILTER ( 'Program', 'Program'[MajorVersion] ),
'Program'[MajorVersion], DESC
),
TRUE (),
FALSE ()
)
Table example:
ID MajorVersion
1 18.4
2 18.4
3 18.5
4 18.2
5 18.6
6 18.6
7 18.4
8 18.4
9 18.6
10 18.6
Desired Result: (Since the top 2 values within MajorVersion would be 18.5 & 18.6)
ID MajorVersion isCurrent
1 18.4 FALSE
2 18.4 FALSE
3 18.5 TRUE
4 18.2 FALSE
5 18.6 TRUE
6 18.6 TRUE
7 18.4 FALSE
8 18.4 FALSE
9 18.6 TRUE
10 18.6 TRUE
powerbi dax
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
asked Nov 16 '18 at 19:30
user3893312user3893312
104
add a comment |
I'm sure I'm missing something simple here. But I'm having no luck.
What I'd like to do is to create a new TRUE/FALSE column based upon a value within a column being in the top 2 results of the same column.
This snippet currently works for the "Max Value"
isCurrent =
IF (
'Program'[MajorVersion] = MAXX ( 'Program', 'Program'[MajorVersion] ),
TRUE (),
FALSE ()
)
What I'd prefer working is not just the MAXX value but the top 2 values within that column. So I am using the TOPN function below. Unfortunately, this is not working.
isCurrent =
IF (
'Program'[MajorVersion]
IN TOPN (
2,
FILTER ( 'Program', 'Program'[MajorVersion] ),
'Program'[MajorVersion], DESC
),
TRUE (),
FALSE ()
)
Table example:
ID MajorVersion
1 18.4
2 18.4
3 18.5
4 18.2
5 18.6
6 18.6
7 18.4
8 18.4
9 18.6
10 18.6
Desired Result: (Since the top 2 values within MajorVersion would be 18.5 & 18.6)
ID MajorVersion isCurrent
1 18.4 FALSE
2 18.4 FALSE
3 18.5 TRUE
4 18.2 FALSE
5 18.6 TRUE
6 18.6 TRUE
7 18.4 FALSE
8 18.4 FALSE
9 18.6 TRUE
10 18.6 TRUE
powerbi dax
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
asked Nov 16 '18 at 19:30
user3893312user3893312
104
I'm sure I'm missing something simple here. But I'm having no luck.
What I'd like to do is to create a new TRUE/FALSE column based upon a value within a column being in the top 2 results of the same column.
This snippet currently works for the "Max Value"
isCurrent =
IF (
'Program'[MajorVersion] = MAXX ( 'Program', 'Program'[MajorVersion] ),
TRUE (),
FALSE ()
)
What I'd prefer working is not just the MAXX value but the top 2 values within that column. So I am using the TOPN function below. Unfortunately, this is not working.
isCurrent =
IF (
'Program'[MajorVersion]
IN TOPN (
2,
FILTER ( 'Program', 'Program'[MajorVersion] ),
'Program'[MajorVersion], DESC
),
TRUE (),
FALSE ()
)
Table example:
ID MajorVersion
1 18.4
2 18.4
3 18.5
4 18.2
5 18.6
6 18.6
7 18.4
8 18.4
9 18.6
10 18.6
Desired Result: (Since the top 2 values within MajorVersion would be 18.5 & 18.6)
ID MajorVersion isCurrent
1 18.4 FALSE
2 18.4 FALSE
3 18.5 TRUE
4 18.2 FALSE
5 18.6 TRUE
6 18.6 TRUE
7 18.4 FALSE
8 18.4 FALSE
9 18.6 TRUE
10 18.6 TRUE
powerbi dax
powerbi dax
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
asked Nov 16 '18 at 19:30
user3893312user3893312
104
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
asked Nov 16 '18 at 19:30
user3893312user3893312
104
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
edited Nov 16 '18 at 19:43
Alexis Olson
15.6k22136
15.6k22136
asked Nov 16 '18 at 19:30
user3893312user3893312
104
asked Nov 16 '18 at 19:30
user3893312user3893312
104
asked Nov 16 '18 at 19:30
user3893312user3893312
104
104
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There are a couple problems with the way you're trying to do it.
First, TOPN returns the top N rows, not the top N values in a column.
Second, TOPN returns full rows, not just a single column.
To fix both of these, you can do a TOPN on VALUES(Program[MajorVersion]):
isCurrent =
Program[MajorVersion] IN
TOPN(2,
VALUES(Program[MajorVersion]),
Program[MajorVersion],
DESC
)
This already returns True or False without needing the IF.
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
Hi Alexis, Thank you very much for such a quick response. I had wrongfully assumed that since I filtered the table i would only be looking at the 2 rows of the one column. It definitely makes a lot more sense now that I'm looking at what you did. Thanks again and have a great weekend.
– user3893312
Nov 16 '18 at 19:58
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%2f53344233%2fpowerbi-if-value-in-topn-values-then-true%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
There are a couple problems with the way you're trying to do it.
First, TOPN returns the top N rows, not the top N values in a column.
Second, TOPN returns full rows, not just a single column.
To fix both of these, you can do a TOPN on VALUES(Program[MajorVersion]):
isCurrent =
Program[MajorVersion] IN
TOPN(2,
VALUES(Program[MajorVersion]),
Program[MajorVersion],
DESC
)
This already returns True or False without needing the IF.
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
Hi Alexis, Thank you very much for such a quick response. I had wrongfully assumed that since I filtered the table i would only be looking at the 2 rows of the one column. It definitely makes a lot more sense now that I'm looking at what you did. Thanks again and have a great weekend.
– user3893312
Nov 16 '18 at 19:58
add a comment |
There are a couple problems with the way you're trying to do it.
First, TOPN returns the top N rows, not the top N values in a column.
Second, TOPN returns full rows, not just a single column.
To fix both of these, you can do a TOPN on VALUES(Program[MajorVersion]):
isCurrent =
Program[MajorVersion] IN
TOPN(2,
VALUES(Program[MajorVersion]),
Program[MajorVersion],
DESC
)
This already returns True or False without needing the IF.
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
Hi Alexis, Thank you very much for such a quick response. I had wrongfully assumed that since I filtered the table i would only be looking at the 2 rows of the one column. It definitely makes a lot more sense now that I'm looking at what you did. Thanks again and have a great weekend.
– user3893312
Nov 16 '18 at 19:58
add a comment |
There are a couple problems with the way you're trying to do it.
First, TOPN returns the top N rows, not the top N values in a column.
Second, TOPN returns full rows, not just a single column.
To fix both of these, you can do a TOPN on VALUES(Program[MajorVersion]):
isCurrent =
Program[MajorVersion] IN
TOPN(2,
VALUES(Program[MajorVersion]),
Program[MajorVersion],
DESC
)
This already returns True or False without needing the IF.
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
There are a couple problems with the way you're trying to do it.
First, TOPN returns the top N rows, not the top N values in a column.
Second, TOPN returns full rows, not just a single column.
To fix both of these, you can do a TOPN on VALUES(Program[MajorVersion]):
isCurrent =
Program[MajorVersion] IN
TOPN(2,
VALUES(Program[MajorVersion]),
Program[MajorVersion],
DESC
)
This already returns True or False without needing the IF.
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
answered Nov 16 '18 at 19:51
Alexis OlsonAlexis Olson
15.6k22136
15.6k22136
Hi Alexis, Thank you very much for such a quick response. I had wrongfully assumed that since I filtered the table i would only be looking at the 2 rows of the one column. It definitely makes a lot more sense now that I'm looking at what you did. Thanks again and have a great weekend.
– user3893312
Nov 16 '18 at 19:58
add a comment |
Hi Alexis, Thank you very much for such a quick response. I had wrongfully assumed that since I filtered the table i would only be looking at the 2 rows of the one column. It definitely makes a lot more sense now that I'm looking at what you did. Thanks again and have a great weekend.
– user3893312
Nov 16 '18 at 19:58
Hi Alexis, Thank you very much for such a quick response. I had wrongfully assumed that since I filtered the table i would only be looking at the 2 rows of the one column. It definitely makes a lot more sense now that I'm looking at what you did. Thanks again and have a great weekend.
– user3893312
Nov 16 '18 at 19:58
Hi Alexis, Thank you very much for such a quick response. I had wrongfully assumed that since I filtered the table i would only be looking at the 2 rows of the one column. It definitely makes a lot more sense now that I'm looking at what you did. Thanks again and have a great weekend.
– user3893312
Nov 16 '18 at 19:58
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%2f53344233%2fpowerbi-if-value-in-topn-values-then-true%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
