Forum Discussion
RANKX Dax function behavior with different column from same table
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 CurrentDimensionRankThis 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
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
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