Forum Discussion
Rankx ignoring 1 column
Hi All,
I have created a measure wich worked well until I decided to add one column in the table below (the last column). Now the Rankx measure interacts with the last columns. Could you help me fixing the code so that the last column is not taking into account.
Thanks for your help.
Rankx measure:
Product ranking (volumes moy. stockés) =
VAR fx = [Volumes moy. stockés]
RETURN
IF (
fx = 0,
BLANK (),
RANKX ( ALL ( XN_ART_DEPOT[ART/VER] ), [Volumes moy. stockés],, DESC )
)
Attempt - not working:
VAR fx = [Volumes moy. stockés]
RETURN
IF (
fx = 0,
BLANK (),
CALCULATE ( RANKX ( ALL ( XN_ART_DEPOT[ART/VER] ), [Volumes moy. stockés],, DESC ), ALLEXCEPT ( XN_ART_DEPOT, XN_ART_DEPOT[Dubai_stock] ) )
)
You're right Arnault_ , I just updated the measure:
Product ranking (volumes moy. stockés) = VAR fx = [Volumes moy. stockés] RETURN IF ( fx = 0, BLANK (), RANKX ( ALL ( XN_ART_DEPOT[ART/VER], XN_ART_DEPOT[Dubai_stock] ), [Volumes moy. stockés], , DESC ) )Hi All,
I finally figured this out. Here is the correct formula:
Product ranking (volumes moy. stockés) v 3 = VAR fx = [Volumes moy. stockés] RETURN IF ( fx = 0, BLANK (), RANKX ( ALLSELECTED ( XN_ART_DEPOT[ART/VER], XN_ART_DEPOT[Dubai_stock] ), [Volumes moy. stockés],, DESC ) )Thank you for your help.
10 Replies
Hi Arnault_ ,
The issue you're encountering occurs because the ALL function is removing all filters from the XN_ART_DEPOT[ART/VER] column, but when you add a new column (Dubai_stock), it starts affecting the results. This happens because the rank calculation interacts with all columns in the table.Please try this measure:
Product ranking (volumes moy. stockés) = VAR fx = [Volumes moy. stockés] RETURN IF ( fx = 0, BLANK(), CALCULATE( RANKX( ALL(XN_ART_DEPOT[ART/VER]), -- Rank by ART/VER only [Volumes moy. stockés],, DESC ), ALLEXCEPT(XN_ART_DEPOT, XN_ART_DEPOT[Dubai_stock]) -- Keep all other filters except Dubai_stock ) )Let me know if you're facing any issues.
Hi Bibiano_Geraldo ,
Thanks you very much for you help, unfortunately, it does not work. The rank is now "1" for all the products.
Hi Arnault_ ,
Please try the bellow Updated DAX, if the problem persist, consider to share no sensitive data sample:
Product ranking (volumes moy. stockés) = VAR fx = [Volumes moy. stockés] RETURN IF ( fx = 0, BLANK(), RANKX ( FILTER ( ALL ( XN_ART_DEPOT[ART/VER] ), -- Ignore filter on ART/VER NOT ( ISBLANK ( [Volumes moy. stockés] ) ) -- Ensure non-blank values ), [Volumes moy. stockés],, DESC ) )
the new column affects the ranking unexpectedly, consider explicitly excluding it from the calculation using REMOVEFILTERS or KEEPFILTERS to refine the ranking context.
RANKX (
REMOVEFILTERS ( XN_ART_DEPOT[LastColumn] ),
[Volumes moy. stockés],
,
DESC
)
This adjustment should help isolate the RANKX calculation from the impact of the newly added column. Let me know if further clarification is needed!Hi All,
I finally figured this out. Here is the correct formula:
Product ranking (volumes moy. stockés) v 3 = VAR fx = [Volumes moy. stockés] RETURN IF ( fx = 0, BLANK (), RANKX ( ALLSELECTED ( XN_ART_DEPOT[ART/VER], XN_ART_DEPOT[Dubai_stock] ), [Volumes moy. stockés],, DESC ) )Thank you for your help.
