Forum Discussion
Avoid Context When Ranking
Hi ,
I want to rank my table based only on the Name column, even if I bring in any number of columns into the context (like Column2, Column3). The measure I use now removes the context from Column1, Column2 and im getting wat i want, but the ranking is not continuous; it is giving the row number from the temporary table. How can I make it continuous?
The measure I used:
RANK - TEST =
RANKX(
ALL(D_TEST),
CALCULATE(MAX(D_TEST[NAME])),,
ASC,
Dense
)
I also tried using ALLSELECTED instead of ALL. The ranking is wrong, when I have more than one column in context.
Note: The goal is to achieve ranking based only on the first column (based on NAME column in ASC Order) in a matrix table. ignoring other columns in context.
output of the above dax:
| NAME | COLUMN2 | COLUMN3 | RANK - TEST |
| assistant1 | A | Jul-24 | 20 |
| assistant1 | B | Jul-24 | 20 |
| assistant1 | B | Aug-24 | 20 |
| assistant2 | A | Jul-24 | 62 |
| assistant2 | B | Jun-24 | 62 |
| assistant2 | B | Jul-24 | 62 |
| assistant2 | B | Aug-24 | 62 |
| assistant2 | C | Jul-24 | 62 |
| assistant3 | A | Jul-24 | 302 |
| assistant3 | B | Jul-24 | 302 |
| assistant3 | B | Aug-24 | 302 |
Understood, thanks for clarifying Anonymous 🙂
If we call the relevant fact table FactTable, you could write something like this:
RANK = IF ( NOT ISEMPTY ( FactTable ), VAR Names = CALCULATETABLE ( SUMMARIZE ( FactTable, D_TEST[NAME] ), ALLSELECTED () ) RETURN RANK ( DENSE, Names ) )This should return a "dense" rank based solely on D_TEST[NAME]. The ranking is relative to the list of NAME values for which the FactTable is nonempty in the ALLSELECTED() context.
It will only return a rank when FactTable is nonempty.
Does this work for you?
4 Replies
Hi Anonymous
Since you want to rank just by the NAME column, try either of these, assuming you want the rank to be in ascending order of D_TEST[NAME]:
RANK = RANK ( DENSE, ALL ( D_TEST[NAME] ) )RANK = RANK ( DENSE, ALLSELECTED ( D_TEST[NAME] ) )For descending order, add an ORDERBY argument, e.g.
RANK = RANK ( DENSE, ALL ( D_TEST[NAME] ), ORDERBY ( D_TEST[NAME], DESC) )Do the above work for you?
Hi OwenAuger ,
If I use ALL() on top of the test table, the ranking will not be continuous when filtered.
When I use ALLSELECTED, Column2 and Column3 affect the ranking.
And D_TEST is a dimension table, so not all names will have data when filtered. As a result, I’m not getting continuous ranking. How can I overcome this scenario?
Thanks for your Help !Understood, thanks for clarifying Anonymous 🙂
If we call the relevant fact table FactTable, you could write something like this:
RANK = IF ( NOT ISEMPTY ( FactTable ), VAR Names = CALCULATETABLE ( SUMMARIZE ( FactTable, D_TEST[NAME] ), ALLSELECTED () ) RETURN RANK ( DENSE, Names ) )This should return a "dense" rank based solely on D_TEST[NAME]. The ranking is relative to the list of NAME values for which the FactTable is nonempty in the ALLSELECTED() context.
It will only return a rank when FactTable is nonempty.
Does this work for you?