Forum Discussion

rlansing's avatar
rlansing
Resolver I
10 years ago
Solved

RANKX question

Hello,

I am refining a DAX measure that calculates a product's rank within a group of products. Here is my current measure:

$ Rnk = IF([$ Vol] > 0 && HASONEVALUE( Products[Product] ),
   RANKX ( ALL( Products[Sub-Cat], Products[Product], Products[UPC], Products[Brand] ), [$ Vol] ) , BLANK() )

 

I am trying to remove any filter context on the various characteristics of the product, so that an item's rank does not change when the list is filtered. The problem I have is that there are almost 100 product characterstics that could be filtered, and I would like my RANKX measure to maintain a static rank, regardless of the products being filtered. The above measure works great until a column not referenced in the ALL list is filtered.

 

The only filters I want the RANKX measure to obey are "Market" "Date" "Sub-Cat". I have been trying to use ALLEXCEPT, but I cannot get it working, everything keeps showing up as 1.

 

Thanks for the help!

2 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion

    Maybe I am missing something. Why can't you use this

     

    Rankx(all(product), etc

     

     

    • rlansing's avatar
      rlansing
      Resolver I

      Thanks Matt. Now it seems trivial. Because I just want to honor the filter on the Sub-Category, I am now using

      RANKX (ALLEXCEPT (Products, Products[Sub-Cat]), [$ Vol] ) etc....

       

      I was trying to use ALL but applied it to the Fact table not the dimension table.