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 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 !