Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
1 year ago
Solved

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 fix...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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_'s avatar
    Arnault_
    1 year ago

    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's avatar
Bibiano_Geraldo
Super User
1 year ago

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_'s avatar
    Arnault_
    Resolver III
    1 year ago

    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's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

      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_'s avatar
        Arnault_
        Resolver III
        1 year ago

        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.