The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
Understood, thanks for clarifying @Abishek 🙂
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 @Abishek
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 @Abishek 🙂
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 !
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |