Forum Discussion

3maris's avatar
3maris
Frequent Visitor
3 years ago

Is RANKX the right answer to this problem?

Hello,

I have a dashboard where the user can select 2 different date ranges and compare the same metric.

Everything is working fine but now I would like to plot these data in a line chart for comparison.

The line chart would use the date as x-axis but with the current data, the lines will be plotted on different date making the comparison really hard.

I thought that using a RANKX function could help. The idea would be to create a rank based on the value of metric 1 or metric 2 and order the date ASC.
I would then use the ranking column as the x-axis. 

This is what I would like to archive 
ranking
If using RANKX is not the right thing to do, what would you suggest?
Any help would be great, I'm just starting to use PBI

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi 3maris ,

    You can create a measure as below to get it, please find the details in the attachment.

    Rank = 
VAR _selcountryIso =
    SELECTEDVALUE ( 'Table'[countryIso] )
VAR _rankv1 =
    RANKX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[countryIso] = _selcountryIso
                && [metric 1] <> BLANK ()
        ),
        CALCULATE ( MAX ( 'Table'[date] ) ),,ASC
    )
VAR _rankv2 =
    RANKX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[countryIso] = _selcountryIso
                && [metric 2] <> BLANK ()
        ),
        CALCULATE ( MAX ( 'Table'[date] ) ),,ASC
    )
VAR _rank1 =
    IF ( [metric 1] = BLANK (), BLANK (), _rankv1 )
VAR _rank2 =
    IF ( [metric 2] = BLANK (), BLANK (), _rankv2 )
VAR _rank =
    IF ( ISBLANK ( _rank1 ), _rank2, _rank1 )
RETURN
    IF ( ISINSCOPE ( 'Table'[date] ), _rank, BLANK () )

    Best Regards

  • 3maris's avatar
    3maris
    Frequent Visitor
    3 years ago

    Hi Anonymous ,

    first of all, thank you so much for your help.
    I can see that your solution is working in your example file, so I've tried to apply it to my user scenario but it's not working 100%.

    Let me try to explain my case.

    I have 1 single table with with metric_1, and metric_2 by country and date.
    On this table I have 2 date slider, that work on the same date dimension.


    Date range 1 slicer has an active relationship with the table while slicer 2 has an inactive relationship.
    If you look at the Rank 1 measurement your code is working perfectly, it is considering only the dates with metric_1 > 0 and the rank is correct.
    On Rank_2 things get interesting...
    The formula of rank 2 is the same as rank 1, it just considers the metric_2. 
    The ranking is correct only for the overlapping days but not for the remaining date_range_2 days that are not included in the date range 1 slicer.

    It looks like that the ranking does not work properly for the date that are not included in the date range 1 slicer.
    Here is an example of full overlap on date range slicer:


    And another example with no days overlapping