Forum Discussion

Icon for Resolver II rank

Resolver II

4 years ago

Solved

Add ranking in table with DAX

Hi All,

I would like to add a rank by Amount in the following table.

I tried to add the ranking with the following codes but the ranking is not in proper order.

Rank = RANKX(ALL(FactTable[ClaimType],FactTable[Diagnosis]), [Amount],,DESC, Dense)

where [Amount] = CALCULATE(SUM(FactTable[Amount]))

Previously, the ranking is worked with ALLSELECTED function, but now it shows curly line under the second column.

RANKX ( ALLSELECTED('FactTable'[ClaimType], 'FactTable'[Diagnosis]), [Amount],,DESC )

Can any one help?

Thanks

PowerBIOrg
4 years ago
Hi amitchandak , your DAX works if I removed the "Case" column.

In order for me to show "Case" column, I modified my DAX as following:
# Case =
SWITCH(TRUE(),
NOT(ISBLANK(RANKX (summarize( ALLSELECTED(FactTable),FactTable[ClaimType],FactTable[Diagnosis]), 'Measure'[Amount],,DESC,Dense))),
[Case])

My ranking order is perfect now.

Thank you!

4 Replies

amitchandak
Super User
4 years ago
PowerBIOrg , Try like

RANKX ( Summarize(ALLSELECTED('FactTable'),'FactTable'[ClaimType], 'FactTable'[Diagnosis]), [Amount],,DESC )
- PowerBIOrg
  Resolver II
  4 years ago
  Hi amitchandak , the ordering still having issue. 😥
- PowerBIOrg
  Resolver II
  4 years ago
  Hi amitchandak , your DAX works if I removed the "Case" column.
  
  In order for me to show "Case" column, I modified my DAX as following:
  # Case =
  SWITCH(TRUE(),
  NOT(ISBLANK(RANKX (summarize( ALLSELECTED(FactTable),FactTable[ClaimType],FactTable[Diagnosis]), 'Measure'[Amount],,DESC,Dense))),
  [Case])
  
  My ranking order is perfect now.
  
  Thank you!
Jihwan_Kim
Super User
4 years ago
Hi,

I am not sure how your data model looks like, but try to include all columns that are realted to ClaimType-Column and Diagnosis-Column. For instance, try to find the sorting-columns for the ClaimType and Diagnosis columns and try to includ these in the measure.