topic Re: RANKX ASC incorrectly starting at 2 in DAX Commands and Tips

RANKX ASC incorrectly starting at 2

greenskmachine2 — Sun, 15 Sep 2024 23:47:23 GMT

Hi there,

I am, for the first time, using RANKX for reporting different data both in a descending and an ascending way.
I've had no issues with DESC, but for some reason ASC always starts at 2. It seems that everything else that's in my DimBusiness table, that I'm not interested in/is filtered out, becomes a 1. I don't know how to manage it. Obviously my filters are wrong.

I'm using this:

Ranking Ave =
RANKX( ALL(DimBusiness[BusinessName]), CALCULATE( [Weighted Ave],
KEEPFILTERS( DATESBETWEEN( Dim[CalendarDate], [ReportingStartDate], [ReportingEndDate] ) ) ),
, ASC, DENSE )

Others have had the same issues, but I can't wrap my head around it. Help much appreciated.

Re: RANKX ASC incorrectly starting at 2

powerbiexpert22 — Mon, 16 Sep 2024 00:40:00 GMT

Hi greenskmachine2 ,

the reason could be the presence of blank values in your measure [Weighted Ave]. please share sample data if possible

Re: RANKX ASC incorrectly starting at 2

Jihwan_Kim — Mon, 16 Sep 2024 03:21:16 GMT

Hi,

I am not sure if I understood your question correctly, but please try something like below.

Ranking Ave = VAR _t = FILTER ( ALL ( DimBusiness[BusinessName] ), ( NOT [Weighted Ave] = 0 ) ) RETURN RANKX ( _t, CALCULATE ( [Weighted Ave], KEEPFILTERS ( DATESBETWEEN ( Dim[CalendarDate], [ReportingStartDate], [ReportingEndDate] ) ) ), , ASC, DENSE )

Re: RANKX ASC incorrectly starting at 2

greenskmachine2 — Mon, 16 Sep 2024 04:38:37 GMT

This seems to have worked. Thanks very much.

Are you able to explain what the issue was?

Re: RANKX ASC incorrectly starting at 2

Jihwan_Kim — Mon, 16 Sep 2024 05:42:25 GMT

Hi,

I tried to create a virtual table, inside the measure, that does not contain zero value of [Weighted Ave]. And then, I tried to rank among the virtual table.

If you want to change the condition, you can try altering the below part.

VAR _t =
FILTER ( ALL ( DimBusiness[BusinessName] ), ( NOT [Weighted Ave] = 0 ) )

I hope this explains.

Thank you.