topic Re: RANKX Dax function behavior with different column from same table in DAX Commands and Tips

RANKX Dax function behavior with different column from same table

DeepakSharma_01 — Tue, 19 Nov 2024 05:36:19 GMT

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

)

Re: RANKX Dax function behavior with different column from same table

shafiz_p — Tue, 19 Nov 2024 06:08:44 GMT

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

Re: RANKX Dax function behavior with different column from same table

DeepakSharma_01 — Tue, 19 Nov 2024 14:14:04 GMT

Thank You so much Mr. Shafiz,

It's working fine.

Re: RANKX Dax function behavior with different column from same table

DeepakSharma_01 — Thu, 21 Nov 2024 07:18:17 GMT

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

Re: RANKX Dax function behavior with different column from same table

shafiz_p — Thu, 21 Nov 2024 07:47:03 GMT

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

Re: RANKX Dax function behavior with different column from same table

DeepakSharma_01 — Thu, 21 Nov 2024 08:44:41 GMT

Hi shafiz_p ,

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

On Multiple selections

Re: RANKX Dax function behavior with different column from same table

shafiz_p — Thu, 21 Nov 2024 09:07:13 GMT

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