Forum Discussion

george_o0802's avatar
george_o0802
Regular Visitor
2 years ago

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

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.

 

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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 ) )