Forum Discussion
How to make RANKX function rank based only on the first row group and exclude the other row groups
Hi,
I have a rank function that needs to be calculated based only on Column1. Even if I add two or three other columns, the ranking should always be based on Column1. How can I ensure that my DAX formula ignores all columns except Column1 for ranking ?
Note: Column1, Column2, and Column3 are from different dimension tables.
MEASURE :
RANK =
VAR COLUMN1 =
FILTER(
ALL(D_COLUMN1_TABLE),
CALCULATE( SUM(F_SCORE[SCORE]) ALL(D_COLUMN2_TABLE) ) <> 0
)
RETURN
RANKX(
COLUMN1, CALCULATE(MAX(D_COLUMN1_TABLE[NAME]))
,
,
ASC,
Dense
)
This is the result i got:
2 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - AlexisOlson
Can you simplify it to this?
VAR COLUMN1 = FILTER ( ALL ( D_COLUMN1_TABLE[NAME] ), CALCULATE ( SUM ( F_SCORE[SCORE] ), ALL (), VALUES ( D_COLUMN1_TABLE[NAME] ) ) <> 0 ) RETURN RANKX ( COLUMN1, D_COLUMN1_TABLE[NAME], , ASC, DENSE )P.S. I generally recommend using the newer RANK function instead of RANKX.
