PowerBI IF value IN TOPN values then TRUE

Question

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

Alexis Olson Alexis Olson · Accepted Answer · 2018-11-16T19:51:37

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.

PowerBI IF value IN TOPN values then TRUE

1 Answers