Is RANKX the right answer to this problem?

[3maris]

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

[3maris]

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

[Anonymous]

Hi @3maris ,

In order to get a better understanding on your requirement and give you a suitable solution, could you please provide some raw data in your table (exclude sensitive data) with Text format, visual settings(slicers&matrix) and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[3maris]

Hi @Anonymous ,

here is the pbix file https://www.dropbox.com/s/7s6v43dqlr358iy/Ranking_PBI.pbix?dl=0

I've also added an example of why I'm trying to get the rank.
I'm still not 100% sure this is the best option to get to it.
Thanks again for your time!

[Anonymous]

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