Forum Discussion

DeepakSharma_01's avatar
DeepakSharma_01
Frequent Visitor
1 year ago
Solved

RANKX Dax function behavior with different column from same table

Hi all,

 

I have written a DAX to rank a measure, which I'll use with different columns but the result is Orpos_item_ID is working fine but with other columns from the same table it is not working.

 

Rank4 =
RANKX(
    ALLSELECTED('Item category'),
    CALCULATE(
        [$ OOS_Value.],
        'Item category'[ITEM_TYPE] IN { "Regular", "Hold PO" },
        'Item category'[ITEM_PURCHASING] = "Regular"
    ),
    ,
    DESC,Dense
)

 

 
 
 
 
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi DeepakSharma_01  You could try ISINSCOPE function to evaluate current dimention and rank. See an example code:

     

    Rank = 
VAR CurrentDimensionRank = 
    SWITCH(
        TRUE(),
        ISINSCOPE(DimProduct[Color]), RANKX(ALL(DimProduct[Color]), [Revenue], ,DESC, Dense),
        ISINSCOPE(DimProduct[ModelName]), RANKX(ALL(DimProduct[ModelName]), [Revenue], ,DESC, Dense),
        ISINSCOPE(DimProduct[ProductLine]), RANKX(ALL(DimProduct[ProductLine]), [Revenue], ,DESC, Dense)
    )
RETURN
CurrentDimensionRank

     

     

    This measure will work for all 3 dimention color, ModelName, and ProductLine.


    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

     

    Best Regards,
    Shahariar Hafiz

6 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi DeepakSharma_01  You could try ISINSCOPE function to evaluate current dimention and rank. See an example code:

     

    Rank = 
VAR CurrentDimensionRank = 
    SWITCH(
        TRUE(),
        ISINSCOPE(DimProduct[Color]), RANKX(ALL(DimProduct[Color]), [Revenue], ,DESC, Dense),
        ISINSCOPE(DimProduct[ModelName]), RANKX(ALL(DimProduct[ModelName]), [Revenue], ,DESC, Dense),
        ISINSCOPE(DimProduct[ProductLine]), RANKX(ALL(DimProduct[ProductLine]), [Revenue], ,DESC, Dense)
    )
RETURN
CurrentDimensionRank

     

     

    This measure will work for all 3 dimention color, ModelName, and ProductLine.


    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

     

    Best Regards,
    Shahariar Hafiz

  • DeepakSharma_01's avatar
    DeepakSharma_01
    Frequent Visitor
    1 year ago

    Hi shafiz_p ,
    I've written below metioned DAX, It is working fine on indivisual selections but when I'm selecting multiple columns then it is not working.

    Rank =
    VAR Dim_Rank = 
        SWITCH(TRUE(),
            ISINSCOPE('Item category'[ORPOS_ITEM_ID]), RANKX(ALL('Item category'[ORPOS_ITEM_ID]), [$ OOS_Value.], ,DESC, Dense),
            ISINSCOPE('Item category'[ITEM_DIVISION]), RANKX(ALL('Item category'[ITEM_DIVISION]), [$ OOS_Value.], ,DESC, Dense),
            ISINSCOPE('Item category'[ITEM_DEPARTMENT]), RANKX(ALL('Item category'[ITEM_DEPARTMENT]),[$ OOS_Value.], ,DESC, Dense),
            ISINSCOPE('Item category'[MAIN_CATEGORY]), RANKX(ALL('Item category'[MAIN_CATEGORY]), [$ OOS_Value.], ,DESC, Dense)
        )
    RETURN Dim_Rank

     

     



    • shafiz_p's avatar
      shafiz_p
      Super User
      1 year ago

      Hi DeepakSharma_01  Formula will work for only one dimension at a time. But if you place multiple dimention this will not work. 

      Now question is what you want to achieve. Are you trying to rank each department of each division. Meaning rank will reset for each division. See an example of such scenarion:

       

      Here first ranking color wise and then ranking product line of each color. See how ranking resets when color change. If this is the case try update your formula as the below code:

      Rank =
VAR CurrentDivision = SELECTEDVALUE('Item category'[ITEM_DIVISION])
RETURN
SWITCH(
    TRUE(),
    ISINSCOPE('Item category'[ITEM_DEPARTMENT]), 
        RANKX(
            FILTER(
                ALL('Item category'[ITEM_DEPARTMENT], 'Item category'[ITEM_DIVISION]),
                'Item category'[ITEM_DIVISION] = CurrentDivision
            ),
            [$ OOS_Value.], 
            ,DESC, 
            Dense
        ),
    ISINSCOPE('Item category'[ITEM_DIVISION]), 
        RANKX(
            ALL('Item category'[ITEM_DIVISION]), 
            [$ OOS_Value.], 
            ,DESC, 
            Dense
        )
)

       

      It will work for both Item_Division and Item_Department. If you need to do for other other hirarchy, please change column names accordingly. You could use nested switch for each pair.

      Try use matrix rather table.

      Hope this it clearify your problem!!

      If, please accept it as a solution and a kudos!!

      Best Regards,
      Shahariar Hafiz