Rankx ignoring 1 column

[Arnault_]

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] ) )
    )

[Bibiano_Geraldo · Accepted solution]

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
        )
    )

[Arnault_ · Accepted solution]

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.

[Bibiano_Geraldo]

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.

[Arnault_]

Hi @Bibiano_Geraldo ,

Thanks you very much for you help, unfortunately, it does not work. The rank is now "1" for all the products.

[Bibiano_Geraldo]

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
        )
    )

[Arnault_]

Hi @Bibiano_Geraldo ,

In the revised formula, you don't remove XN_ART_DEPOT[Dubai_stock], then it goes back to my initial result. I will try to figure this out. I am not sure the sample data would be usable because my measure calls a formula from another table. But anyway, I think you for your help.

[Bibiano_Geraldo · Accepted solution]

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
        )
    )

[Arnault_]

Hey @Bibiano_Geraldo ,

I also found a solution by using ALLSELECTED, but applying the same logic. Which solution you recommand to use? Mine or yours?

[Bibiano_Geraldo]

Hi @Arnault_, It depend on your logic:

Using ALL: This approach ensures that the ranking is calculated by ignoring the “Dubai_stock” column entirely. It is straightforward and effective if you want to exclude specific columns from the ranking calculation.

Using ALLSELECTED: This approach respects the current context and filters applied to the data, which can be useful if you want the ranking to be dynamic based on user selections in the report. It can be more flexible in interactive reports where users might apply different filters.

If your report requires dynamic interaction and you want the ranking to adjust based on user selections, go with ALLSELECTED. If you need a static ranking that ignores specific columns, use ALL.

[Arnault_]

Thanks that's extremely useful !!! I will follow your advise.

[shreebidwai]

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!