Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Issue with RANK measure showing 1 for all Top N values when there is no data

Hello - I have a situation where I am creating a ranking bump chart to show a ranking of an category over time.  I want the users to have the ability to swap between the measures shown on the chart, for which I am using field parameters to manage the swap.  The issue I am having is that one rank measure will have data shown for future dates, and the other measures will not.  When the user swaps to the "non-future" rank measure, this gets shown: 

 

Because there isn't data for the category after April 2023 it is assigning a rank of 1 for all categories, which is technically "correct", but not the desired result in the visual.  What I need this visual to do is ignore all the future dates and only show the rankings up to the dates where data is available, while also being able to swap to the "future measure" where there will be data up to Sept 2023, and relevant to the user requirements.  I'm hoping to avoid doing this with bookmarks as it will be about 6 or 7 measures to choose from on the swap.  

This is the DAX that I am using for the rank measures: 

Rank Non-Future Category = 
VAR SelectedTop =
    SELECTEDVALUE ( 'Top N Ranking'[Top N Ranking] ) //Adjustable Value parameter for Top N
VAR _rank =
    IF (
        ISINSCOPE ( Table[Category] ),
        RANK (
            ,
            ALL ( Table[Category ),
            ORDERBY ( [Measure], DESC )
        ) 
    )
   RETURN
    SWITCH (
        TRUE (),
        SelectedTop = 0, _rank,
        CALCULATE ( _rank ) <= SelectedTop, _rank
    )     


I've tried various manners date filtering on the measure, but it gets ignored in the context, resulting in the image above.  Any ideas on how to get this to work without resorting to bookmarks to swap the mesaure? 

 

2 Replies

  • Return BLANK() as the measure result for all dates you want to suppress.

     

    Have you considered using a Ribbon Chart instead?

    • Anonymous's avatar
      Anonymous
      Not applicable

      I'll try throwing a BLANK() in there for the date supression to see if it works.  I don't want to use the ribbon chart as it misleading in my context, (wider bands for lower ranks, difficult to read), and I'd have to add a -1 to the measure to ensure that #1 apears at the top of the chart instead of the bottom.  Thanks!