Top 5, Bottom 5 rank changes. How do I remove duplicates (using measures, not calculated columns)

[george_o0802]

I have a series of product names, their RSV and their RSV rank compared with the other products in my table. Below is the visualisation I have created. This shows the top and bottom 5 biggest rank changes year on year (I have used a simple topn filter)

The problem is, there are duplicates and I do not want any duplicates. I do not really care which title comes up, just so long as there are only 5 titles. I have read a lot about the best way to get rid of duplicates is to use a second metric to calculate a rank so, if this is the solution, I would want the second metric to be overall rsv. Rather unhelpfully, I guess I would be creating a rank of my RSV rank changes and having the second metric to calculate this rank being RSV.

My rank has been calculated as follows.

RSV Rank = RANKX(ALL('xxx Issue Details'[Title]),[Total RSV], ,DESC,Skip)

RSV Rank for Latest =

CALCULATE([RSV Rank], 'Time Period (Connector)'[Period] IN { "Latest" })

RSV Rank for Previous =

CALCULATE([RSV Rank], 'Time Period (Connector)'[Period] IN { "Previous" })

RSV Rank Change = [RSV Rank for Previous] - [RSV Rank for Latest]

In short, I just want a set of top 5 and bottom 5 titles by rank change and when there are duplicate values I don't want them to appear. I always want 5 titles.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

WINDOW function (DAX) - DAX | Microsoft Learn

Expected result measure: =
VAR _topfive =
    WINDOW (
        1,
        ABS,
        5,
        ABS,
        ALL ( Title[Title] ),
        ORDERBY ( CALCULATE ( SUM ( Data[Value] ) ), DESC, Title[Title], ASC )
    )
VAR _bottomfive =
    WINDOW (
        1,
        ABS,
        5,
        ABS,
        ALL ( Title[Title] ),
        ORDERBY ( CALCULATE ( SUM ( Data[Value] ) ), ASC, Title[Title], ASC )
    )
VAR _list =
    UNION ( _topfive, _bottomfive )
RETURN
    CALCULATE ( SUM ( Data[Value] ), FILTER ( Title, Title[Title] IN _list ) )