topic Re: Is RANKX the right answer to this problem? in DAX Commands and Tips

Is RANKX the right answer to this problem?

3maris — Fri, 03 Mar 2023 13:45:32 GMT

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

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

Re: Is RANKX the right answer to this problem?

Anonymous — Mon, 06 Mar 2023 02:49:50 GMT

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

Re: Is RANKX the right answer to this problem?

3maris — Mon, 06 Mar 2023 18:02:08 GMT

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

Re: Is RANKX the right answer to this problem?

Anonymous — Tue, 07 Mar 2023 06:32:13 GMT

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

Re: Is RANKX the right answer to this problem?

3maris — Tue, 07 Mar 2023 08:14:16 GMT

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!