Indicate RANK 1 Items Among Groups

[AC_DATA]

I am trying to write some DAX for a calculated column that will return the top-ranked item in its group. In the case of the sample data below, I only want to consider records with the STATUS = "SOLD" and I want to indicate which record has the most recent value for DATE by ITEM.

Here is the sample data:

UID	ITEM	STATUS	DATE
1	APPLE	SOLD	8/4/2019
2	APPLE	SOLD	8/11/2019
3	APPLE	BOUGHT	8/19/2019
4	APPLE	SOLD	8/21/2019
5	APPLE	BOUGHT	8/17/2019
6	ORANGE	BOUGHT	7/29/2019
7	ORANGE	SOLD	8/6/2019
8	ORANGE	SOLD	7/26/2019
9	ORANGE	SOLD	7/24/2019
10	CHERRY	BOUGHT	8/17/2019
11	CHERRY	SOLD	8/15/2019
12	CHERRY	BOUGHT	8/11/2019
13	CHERRY	SOLD	7/31/2019
14	CHERRY	SOLD	7/26/2019
15	PEACH	SOLD	8/8/2019
16	PEACH	SOLD	7/23/2019
17	PEACH	SOLD	8/1/2019
18	BANANA	SOLD	8/2/2019
19	BANANA	BOUGHT	8/17/2019
20	GRAPE	SOLD	8/21/2019

And here is the desired outcome:

UID	ITEM	STATUS	DATE	RANK
1	APPLE	SOLD	8/4/2019	
2	APPLE	SOLD	8/11/2019	
3	APPLE	BOUGHT	8/19/2019	
4	APPLE	SOLD	8/21/2019	1
5	APPLE	BOUGHT	8/17/2019	
6	ORANGE	BOUGHT	7/29/2019	
7	ORANGE	SOLD	8/6/2019	1
8	ORANGE	SOLD	7/26/2019	
9	ORANGE	SOLD	7/24/2019	
10	CHERRY	BOUGHT	8/17/2019	
11	CHERRY	SOLD	8/15/2019	1
12	CHERRY	BOUGHT	8/11/2019	
13	CHERRY	SOLD	7/31/2019	
14	CHERRY	SOLD	7/26/2019	
15	PEACH	SOLD	8/8/2019	1
16	PEACH	SOLD	7/23/2019	
17	PEACH	SOLD	8/1/2019	
18	BANANA	SOLD	8/2/2019	1
19	BANANA	BOUGHT	8/17/2019	
20	GRAPE	SOLD	8/21/2019	1

Any help or direction would be appreciated.

[AlB · Accepted solution]

Hi @AC_DATA 

Try this:

Col =
VAR Rank_ =
    RANKX (
        FILTER (
            Table1;
            Table1[ITEM] = EARLIER ( Table1[ITEM] )
                && Table1[STATUS] = "SOLD"
        );
        Table1[DATE];
        ;
        DESC
    )
RETURN
    IF ( Rank_ = 1 && Table1[STATUS] = "SOLD"; Rank_ )

Please mark as solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[dax · Accepted solution]

Hi Ac_DATA,

You also could try below measure

Measure 8 =
VAR maxd =
    CALCULATE (
        MAX ( 'rank'[date] ),
        FILTER ( ALLEXCEPT ( 'rank', 'rank'[item] ), 'rank'[status] = "SOLD" )
    )
RETURN
    IF ( MIN ( 'rank'[date] ) = maxd, 1, "" )

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB · Accepted solution]

Hi @AC_DATA 

Try this:

Col =
VAR Rank_ =
    RANKX (
        FILTER (
            Table1;
            Table1[ITEM] = EARLIER ( Table1[ITEM] )
                && Table1[STATUS] = "SOLD"
        );
        Table1[DATE];
        ;
        DESC
    )
RETURN
    IF ( Rank_ = 1 && Table1[STATUS] = "SOLD"; Rank_ )

Please mark as solved when we get to the solution and consider kudoing if posts are helpful.

Cheers