Forum Discussion
Avoid Context When Ranking
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?
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?
I'm glad the formula worked, Thank You !