Ranking to fetch only 3 values even when there are ties in rank.

[RamSaladi]

Hi All

I have data simlar to this.

Aggrement No,Emp Start Date,Emp EnDate,Reason code
Agg 001,1/1/2013,12/20/2014,Reason 1
Agg 002,1/2/2013,12/21/2014,Reason 2
Agg 003,1/3/2013,12/22/2014,Reason 2
Agg 004,1/4/2013,12/23/2014,Reason 3
Agg 005,1/5/2013,12/24/2014,Reason 4
Agg 006,1/6/2013,12/25/2014,Reason 3
Agg 007,1/7/2013,12/26/2014,Reason 1
Agg 008,1/8/2013,12/27/2014,Reason 5
Agg 009,1/9/2013,12/28/2014,Reason 3
Agg 010,1/10/2013,12/29/2014,Reason 4
Agg 011,1/11/2013,12/27/2014,Reason 6
Agg 012,1/11/2013,12/27/2014,Reason 7
Agg 013,1/11/2013,12/27/2014,Reason 3
Agg 014,1/11/2013,12/27/2014,Reason 1
Agg 015,1/11/2013,12/27/2014,Reason 2
Agg 016,1/11/2013,12/27/2014,Reason 4
Agg 017,1/11/2013,12/27/2014,Reason 5
Agg 018,1/11/2013,12/27/2014,Reason 6

There is a disjointed date table where I will select a date and I am filtering the data for past 1 year based on the date selected in filter. I need to fetch the top 3 reasons and the count has to be exactly 3 even when there are ties.

I have the following measures to calculate rank.

CountReasonCode = CALCULATE( COUNT(Sheet1[Reason code]), FILTER(Sheet1, Sheet1[EndDate] >= [FromDate] && Sheet1[Emp EnDate] <=[ToDate]))

Rank = RANKX(ALLSELECTED(Sheet1[Reason code]), [ReasonCode], , DESC, Dense)

since my rank has ties the values are that i get in visulization not exactly 3. Is there a way to get exactly 3 records for the specific period applied in filter. like the ones highlighted in the screenshot below.

Thanks and Regards,
Ram

[poweraddict]

I also have same issue. If someone can provide solution will be really helpful..

[v-piga-msft]

Hi @RamSaladi ,

For your requirement, you could refer to the measure below.

Measure 2 =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        ISONORAFTER (
                'Table'[CountReasonCode], SELECTEDVALUE ( 'Table'[CountReasonCode] ), ASC,
                'Table'[Reason Code], SELECTEDVALUE ( 'Table'[Reason Code] ), DESC
        )
    )
)

Here is the output.

More details, please refer to my attachment.

Best Regards,

Cherry

[RamSaladi]

Hi @v-piga-msft 

The solution works great except for the we need to filter the data based on the date and need to fetch the data for a year that the user selects in the slicer. So for the same reason count of reasons codes in particular period is a measure. We cannot use measure in SELECTEDVALUE.

Regards
Ram