RANKX Dax function behavior with different column from same table

[DeepakSharma_01]

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

)

 

[DeepakSharma_01]

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]

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

[DeepakSharma_01]

Hi @shafiz_p ,

I want to achieve this in a table,
On single selection

On Multiple selections

[shafiz_p]

Look you can use matrix in a way that looks like a table. See example how to represent matrix like table:

If you want to repeat header, you can turn on repeat row headers. See example:

In matrix you will be able to sort accordingly. But if you need to use table, yes, you can. You need to sort using Shift+Clicking. See example:

Hope this helps!!

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

Best Regards,
Shahariar Hafiz

[DeepakSharma_01]

Thank You so much Mr. Shafiz,

It's working fine.

[shafiz_p · Accepted solution]

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